Copyright: Exl-Plan © Copyright Invest-Tech 1999
Citation: Exl-Plan © Copyright Invest-Tech Limited 1999. All rights reserved. Web site:  www.planware.org


Exl-Plan Help Contents
Contents

Licence Agreements

Licence Agreements

Help System

Very Quick Start

Very Quick Start Procedures

List of Toolbar Buttons

List of Toolbar Buttons

Creating a Plan within 15 Minutes

Creating a Plan within 15 Minutes

Welcome to Exl-Plan

Welcome to Exl-Plan

Powerful & Easy-to-Use

Typographical Conventions

About Invest-Tech

Financial Modelling & Business Planning

Financial Modelling & Business Planning

Computers & Modelling

Uses of a Model

Preparing to Plan

Pitfalls & Dangers

More about Business Planning

Introducing Exl-Plan

Introducing Exl-Plan

Versions of Exl-Plan

Scope of Exl-Plan

Facilities & Features of Exl-Plan

Assumptions Handled by Exl-Plan

Loading Exl-Plan for First Time

Loading Exl-Plan for First Time

Getting Started with Exl-Plan

Getting Started with Exl-Plan

Protecting Your Work

Printing

Zoom Settings

Fast Entry of Assumptions

Exl-Plan's Toolbar

Freezing Titles

Using Exl-Plan

Using Exl-Plan

Starting to Use Exl-Plan

Structuring a Model

Using Quik-Plan

Model-Building Procedure

Avoiding Calculation Errors

Doing Sensitivity Analyses

Viewing What-If Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Tracking Trading Performance

Analysing Long-Term Performance

Changing Exl-Plan

Changing Exl-Plan

Making Simple Changes

Making Complex Changes

Extending Exl-Plan

Support & Assistance

Support Policy for Exl-Plan

Model Development Services

Other Products

Contacting Invest-Tech

Lists of Reports & Graphs

List of Reports

List of Graphs

Adapting Exl-Plan to Different Business Types

Adapting Exl-Plan for Different Business Types

Adapting Exl-Plan for Manufacturing Businesses

Adapting Exl-Plan for Distribution Businesses

Adapting Exl-Plan for Service Businesses

Adapting Exl-Plan for Hybrid & Multi-site Businesses

How Quik-Plan Handles Assumptions

How Quik-Plan Handles Assumptions

Guidance on Entering Assumptions

Guidance on Entering Assumptions

Monthly Assumption Report No. 1 - Sales & Finished Stock Targets

Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales

Monthly Assumption Report No. 4 - Overheads

Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants

Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors

Opening Balance Sheet

Prior Year P&L

Assumptions for 2nd & 3rd Years and Projections for 1st Year - Sales & Costs

Assumptions for 2nd & 3rd years and Projections for 1st Year - Other Items

4th & 5th Year Assumptions

Calculation Error Messages

Calculation Error Messages

User Registration Form

User Registration Form

Shareware Upgrade

Shareware Upgrade


Licence Agreements - Licence Agreements
Licence Agreements

This topic sets out the Licence Agreements for the Commercial and Shareware Versions of Exl-Plan.

NOTICE TO USERS: CAREFULLY READ THE FOLLOWING LEGAL AGREEMENTS. USE OF  EXL-PLAN (THE "SOFTWARE") PROVIDED WITH THIS AGREEMENT CONSTITUTES YOUR ACCEPTANCE OF THESE TERMS. IF YOU DO NOT AGREE TO THE TERMS OF THIS AGREEMENT, DO NOT DISTRIBUTE, INSTALL AND/OR USE THIS SOFTWARE. USER'S USE OF THIS SOFTWARE IS CONDITIONED UPON COMPLIANCE BY USER WITH THE TERMS OF THIS AGREEMENT.

Please refer to the following:

Commercial Version Licence Agreement

Shareware Version Licence Agreement

Shareware Distribution Licence Agreement

Association of Shareware Professionals Ombudsman Statement


Commercial Version Licence Agreement

APPLICATION. This agreement applies to the commercial or registered/upgraded version of the SOFTWARE comprising workbook & related files and supporting documentation.

LICENSE GRANT. Invest-Tech Limited grants you a license to use ONE copy of the version of this SOFTWARE on any ONE hardware product for as many licenses as you purchase. "You" means the company, entity or individual whose funds are used to pay the license fee. "Use" means storing, loading, installing, executing or displaying the SOFTWARE. "Hardware product" means computer, network node, server etc.

OWNERSHIP. The SOFTWARE is owned and copyrighted by Invest-Tech Limited. Your license confers no title or ownership in the SOFTWARE and should not be construed as a sale of any right in the SOFTWARE. This license is not transferable to any other hardware product or other company, entity, or individual.

COPYRIGHT. The SOFTWARE is protected by copyright law. You acknowledge that no title to the intellectual property in the SOFTWARE is transferred to you. You further acknowledge that title and full ownership rights to the SOFTWARE will remain the exclusive property of Invest-Tech Limited and you will not acquire any rights to the SOFTWARE except as expressly set forth in this license. You agree that any copies of the SOFTWARE will contain the same proprietary notices which appear on and in the SOFTWARE.

UNAUTHORIZED USE. You may not modify the SOFTWARE or disable any licensing or control features of the SOFTWARE except as an intended part of the SOFTWARE's programming features. You may not use, copy, rent, lease, sell, modify, decompile, disassemble, otherwise reverse engineer, or transfer the SOFTWARE except as provided in this agreement. Any such unauthorised use shall result in immediate and automatic termination of this license. Most specifically, the SOFTWARE's front worksheet and VBA routines must not be altered under any circumstances. The SOFTWARE can be copied by a registered user for backup purposes and for the purpose of creating additional sets of projections provided that all copies of the SOFTWARE remain on the same hardware product which contains the original supplied files. Multi-user licences are available from Invest-Tech Limited.

INSPECTION AND TESTING. You agree and acknowledge that you will thoroughly inspect and test the SOFTWARE for all of your purposes upon commencement of your use.

LIMITED WARRANTY. THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS. INVEST-TECH LIMITED DISCLAIMS ALL WARRANTIES RELATING TO THIS SOFTWARE, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. NEITHER INVEST-TECH LIMITED NOR ANYONE ELSE WHO HAS BEEN INVOLVED IN THE CREATION, PRODUCTION, OR DELIVERY OF THIS SOFTWARE SHALL BE LIABLE FOR ANY INDIRECT, CONSEQUENTIAL, OR INCIDENTAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE SUCH SOFTWARE, EVEN IF INVEST-TECH LIMITED HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES OR CLAIMS. THE PERSON USING THE SOFTWARE BEARS ALL RISK AS TO THE QUALITY AND PERFORMANCE OF THE SOFTWARE. IN NO EVENT SHALL ANY THEORY OF LIABILITY EXCEED THE LICENSE FEE PAID TO INVEST-TECH LIMITED.

NO LIABILITY FOR CONSEQUENTIAL DAMAGES. IN NO EVENT SHALL INVEST-TECH LIMITED OR ITS SUPPLIERS BE LIABLE TO YOU FOR ANY CONSEQUENTIAL, SPECIAL, INCIDENTAL OR INDIRECT DAMAGES OF ANY KIND ARISING OUT OF THE DELIVERY, PERFORMANCE OR USE OF THE SOFTWARE, EVEN IF INVEST-TECH LIMITED HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. IN NO EVENT WILL INVEST-TECH LIMITED'S LIABILITY FOR ANY CLAIM, WHETHER IN CONTRACT, TORT OR ANY OTHER THEORY OF LIABILITY, EXCEED THE LICENSE FEE PAID BY YOU, IF ANY.

SEVERABILITY. In the event of invalidity of any provision of this license, the parties agree that such invalidity shall not affect the validity of the remaining portions of this license.

GOVERNING LAW. This agreement shall be governed by the laws of the Republic of Ireland and any action or proceeding shall be brought only in a COURT located in the Republic of Ireland. This agreement will not be governed by the United Nations Convention on Contracts for the International Sale of Goods, the application of which is expressly excluded.

RESERVED RIGHTS. All rights not expressly granted here are reserved to Invest-Tech Limited.

ENTIRE AGREEMENT. This is the entire agreement between you and Invest-Tech Limited which supersedes any prior agreement or understanding, whether written or oral, relating to the subject matter of this license.

Use of Exl-Plan signifies acceptance of these terms and conditions. Violators of the terms of this agreement may be prosecuted.

See also:

Shareware Version Licence Agreement

Shareware Distribution Licence Agreement

Association of Shareware Professionals Ombudsman Statement


Shareware Version Licence Agreement

SHAREWARE VERSION. These additional terms apply to the unregistered shareware version of the SOFTWARE. This "try-before-you-buy" version of the SOFTWARE is a shareware program and it is not "freeware", "public domain" software etc. Users are granted a licence to use the shareware version only under the terms and conditions specified in this entire agreement.

LIMITED USE. When you first obtain a copy of the unregistered shareware version of the SOFTWARE, you are granted an evaluation period of not more than 30 days, after which time you must register and pay for the SOFTWARE according to the terms and prices discussed in the SOFTWARE's documentation, or you must remove the SOFTWARE from your computer.  The terms of the entire License Agreement for the commercial/registered version apply to upgraded copies of the SOFTWARE.

SUPPORT. Unregistered copies of the SOFTWARE are unsupported. In return for registering/upgrading, a user will be entitled to receive a copy of the latest fully operational version of SOFTWARE with, optionally, its printed manual. In addition, a user gains access to support via mail, phone, fax or e-mail for three calendar months following confirmation of registration by Invest-Tech Limited. After this period, the provision of support will be at Invest-Tech Limited's discretion.

See also:

Commercial Version Licence Agreement

Shareware Distribution Licence Agreement

Association of Shareware Professionals Ombudsman Statement


Shareware Distribution Licence Agreement

SHAREWARE DISTRIBUTION. These additional terms apply to distribution of the unregistered shareware version of the SOFTWARE.

DISTRIBUTION. The following may distribute the unregistered shareware version of the SOFTWARE without further permission provided they convey a complete and unaltered shareware version of the SOFTWARE and provided they make no claim to ownership of the SOFTWARE:

- Private individuals charging no fees.

- Members of Association of Shareware Professionals (ASP) and other shareware associations.

- Other bona fide shareware distributors and directories including FTP and WWW sites, BBSs, user groups, mail-order disk vendors, CD-ROM publishers, file-distribution networks etc.

BUNDLING. In no case may the SOFTWARE be bundled with hardware or other non-shareware software without prior written permission from Invest-Tech Limited.

VIRUSES. All distribution of the SOFTWARE is further restricted with regard to sources which also distribute virus source code and related virus construction/creation materials. It is unlawful to attach any virus to the SOFTWARE and the SOFTWARE may not be made available on any site, CD-ROM, or with any package which makes available or contains viruses, virus source code, virus construction programs, or virus creation material.

TRANSFERABILITY. Permission to distribute the SOFTWARE is not transferable, assignable, saleable, or franchisable. Each entity wishing to distribute the package must independently satisfy the terms of the distribution license.

REVOKATION. Invest-Tech Limited may revoke any distribution permissions granted here, by notifying you in writing.  All rights not expressly granted here are reserved to Invest-Tech.

VENDINFO.DIZ. Consult the VENDINFO.DIZ data record in the original distribution files for all distribution requirements. It is hereby incorporated by reference. Any distribution satisfying all the distribution requirements expressed in that text and data record is hereby authorised. In no case will the SOFTWARE be distributed without its VENDINFO.DIZ file.

See also:

Shareware Version Licence Agreement

Association of Shareware Professionals Ombudsman Statement


Association of Shareware Professionals Ombudsman Statement

Invest-Tech is a member of the Association of Shareware Professionals (ASP). ASP wants to make sure that the shareware principle works for you. If you are unable to resolve a shareware-related problem with an ASP member by contacting the member directly, ASP may be able to help. The ASP Ombudsman can help you resolve a dispute or problem with an ASP member, but does not provide technical support for members' products. Please write to the ASP Ombudsman at 157-F Love Ave, Greenwood, IN 46142 USA, FAX 317-888-2195, or send email to omb@asp-shareware.org.

See also:

Commercial Version Licence Agreement

Shareware Version Licence Agreement

Shareware Distribution Licence Agreement


Help System

The User Registration Form should be completed and returned to Invest-Tech immediately in order to qualify for support etc. See Support Policy for details.

When Exl-Plan's Online Help is first loaded a conventional contents page or dialog appears depending on the version of Windows being used.

When using Exl-Plan's Help, specific procedures are displayed in separate procedural windows which remain open and on top until closed.

Closing Help also closes any open procedural windows.

The Glossary of Terms. refers to the variables used in the Monthly Assumption Reports. Its definitions, accessible by clicking on underscored terms, provide additional guidance on entering assumptions and explanations of formulae. It supplements Guidance on Entering Monthly Assumptions.


Very Quick Start Procedures

These procedures should enable an Excel spreadsheet user to get Exl-Plan up and running with minimal initial instruction. If problems are encountered, refer to Loading Exl-Plan for the First Time or Getting Started with Exl-Plan.

Exl-Plan requires Microsoft Excel for Windows (versions 5, 7, 8, 95, 97, 2000 or higher) running with Windows 3.1 or Windows 95/98/NT on a PC with a 486/50Mhz or higher processor, 16+ MB of memory, VGA or better display and 10 MB disk space.

If Exl-Plan was secured as shareware and subsequently upgraded remotely by Invest-Tech, the upgraded Exl-Plan's workbook file should be kept safe and intact as the primary master version of Exl-Plan. Copies of this file should be created and used for normal working purposes.

When being loaded for the first time, Exl-Plan needs 8 Mb of disk space to enable it to automatically create backup copies (WORKPLAN.XLS and TESTPLAN.XLS) during set up. If this space is not available, Exl-Plan will skip their creation.

Installation takes up to five minutes, depending on the speed of the PC being used. Exl-Plan must always be installed from supplied installation disk(s) or via a downloaded installation file. It will not operate correctly if it is installed by simply transferring an Exl-Plan workbook file from one PC to another.

Full Details:

Installation of Exl-Plan (Windows 3.1)

Installation of Exl-Plan (Windows 95/98/NT)

Familiarisation with Exl-Plan

Using Quik-Plan - Part 1

Using Quik-Plan - Part 2

Building a New Model


Installation of Exl-Plan (Windows 3.1)

1.	Close all other applications.

2.	Confirm that at least 8 Mb of space is free on the hard disk to which Exl-Plan will be installed.

3.	Insert the Exl-Plan Product Disk in a disk drive and use the Run option within File Manager or Program Manager to run the executable file EXLPLAN.EXE by entering A:\EXLPLAN.EXE at the prompt (replace A: by B: if appropriate). Follow on-screen instructions to install Exl-Plan's files into the appropriate sub-directories.
(If Exl-Plan was acquired by downloading from the Internet to a temporary sub-directory, the downloaded executable file should be run and its on-screen instructions followed to install Exl-Plan's files into the appropriate sub-directories.)

4.	Once installation is complete, go to the Exl-Plan Application Group and review any displayed README file.

5.	Load Excel and open the main Exl-Plan workbook file EXLPLAN.XLS located in the sub-directory specified during installation. Once loaded, follow on-screen prompts and then wait a few moments while copies of Exl-Plan are created. These copies, WORKPLAN.XLS and TESTPLAN.XLS, should be used as the primary working copy and for experimentation respectively. EXLPLAN.XLS should be carefully retained as the original master copy.

Note: If Exl-Plan failed to detect the correct size of the screen display being used, use *Setup | Change Default Zoom Value.


Installation of Exl-Plan (Windows 95/98/NT)

When Exl-Plan starts loading, Excel may display a dialog about macros and viruses. If Exl-Plan was secured from a reliable source or directly from Invest-Tech, press the Enable Macros button. Note that Exl-Plan will not load properly if the Disable Macros button is pressed. As loading progresses, a further dialog may appear about the location of macros. This can be disabled or ignored.

1.	Close all other applications.

2.	Confirm that at least 8 Mb of space is free on the hard disk to which Exl-Plan will be installed.

3.	Insert the Exl-Plan Product Disk in a disk drive and use Start | Run to run the executable file EXLPLAN.EXE by entering A:\EXLPLAN.EXE at the Open prompt (replace A: by B: if appropriate). Follow on-screen instructions to install Exl-Plan's files into the appropriate folders.
(If Exl-Plan was acquired by downloading from the Internet to a temporary folder, the downloaded executable file should be run and its on-screen instructions followed to install Exl-Plan's files into the appropriate folders.)

4.	When installation has finished, go to the Exl-Plan menu within the Programs Menu and review any indicated README file.

5.	Load Excel and open the main Exl-Plan workbook file EXLPLAN.XLS located in the folder specified during installation. Once loaded, follow on-screen prompts and then wait a few moments while copies of Exl-Plan are created. These copies, WORKPLAN.XLS and TESTPLAN.XLS, should be used as the primary working copy and for experimentation respectively. EXLPLAN.XLS should be carefully retained as the original master copy.

Note: If Exl-Plan failed to detect the correct size of the screen display being used, use *Setup | Change Default Zoom Value.


Familiarisation with Exl-Plan

When running Exl-Plan with W95/98/NT, it is best to place the taskbar at the bottom of the screen in order to improve the view of reports etc.

1.	To become familiar with the basics and geography of Exl-Plan, take the Quik-Tour (*Exl-Plan Help | Quik-Tour) from within the previously created TESTPLAN.XLS file.

2.	Run the cursor across the menu bar - File, Edit etc. Note that Exl-Plan has added several new menus (*Assumptions, *Protection etc.).

3.	Click the M_S worksheet tab (or the "MA" toolbar button) to go to the monthly assumptions report for sales forecasts etc. Note that assumption values (displayed in blue) should be entered or altered only within the rectangular boxed-in areas opposite the ->> or <<->> arrows. Values shown in black contain formulae and are normally locked (protected).

4.	Try adding or changing some of the supplied assumption values for sales and recalculate (*Tools | Calculate All & Check) to check the model's integrity and to update all the monthly, first-year, quarterly and annual output reports. (Note that Exl-Plan always uses manual calculation).

5.	Click sheet tabs to view the other monthly assumption reports (M_M, M_C etc.) and change/add assumption values as desired.

6.	Use Exl-Plan's menus, worksheet tabs and buttons (especially "<-" and "->") to view the remaining assumption and output reports and charts.

7.	Explore Exl-Plan's help facilities within the *Exl-Plan Help menu.


Using Quik-Plan - Part 1

Quik-Plan is a facility within Exl-Plan that simplifies the preparation of high-level multi-year projections. To use it to compile real or hypothetical trial financial projections, proceed as follows:

1.	Save TESTPLAN.XLS with an appropriate new file name e.g. QUIKPLAN.XLS.

2.	Select *Setup | Basic Model Info and enter basic information about the business to be modelled.

3.	Select *Setup | Model Title and give the model an appropriate title e.g. First Attempt with Quik-Plan.

4.	Select *Tools | Quik-Plan to make first-cut projections. Delete or overwrite the supplied assumptions and then run Quik-Plan with the newly entered values.

5.	Review a selection of the monthly, quarterly and annual assumption & output reports and charts to see for how Quik-Plan operates and Exl-Plan handles its assumptions.

6.	Revise and expand Quik-Plan's assumptions from within the monthly assumption reports and recalculate as desired (select either *Tools | Calculate All & Check or the "C" button on the toolbar).

7.	Experiment with Exl-Plan's data-entry facilities which are located within the *Assumptions menu (i.e. Clear Assumptions, Constant Assumptions etc.) or accessible via buttons in the toolbar (refer to Fast Entry of Assumptions).


Using Quik-Plan - Part 2

1.	Go to the Quarterly Assumptions Report (by clicking the Q_A tab or "QA" button). Observe that monthly projections for the first-year have been included in this report as quarterly values.

2.	Add or change quarterly assumptions for the second and third years. Recalculate and review the quarterly and annual output reports and their associated charts.

3.	If using the Super version of Exl-Plan, go to the 4th-5th Year Assumptions Report (click the 45_A tab). Add or change annual assumptions for the fourth and fifth years and recalculate.

4.	View the Textual Summary Report (TEXT tab or *Output | Textual) and other annual output reports & charts.

5.	Print selected assumption or output reports and charts.

6.	Use the sensitivity analysis facility (*Tools | Sensitivity Analysis) to globally change groups of key monthly and/or quarterly assumptions.

7.	Review Exl-Plan's other planning tools for analysing what-ifs , profitability planning , short-term cashflow planning , performance tracking  and trend analysis .

8.	Continue exploring Exl-Plan's menus etc. and entering or changing assumptions. Use its online help (*Exl-Plan | Help) as appropriate. Refer to Using Exl-Plan and Guidance on Entering Assumptions for further guidance.


Building a New Model

To start constructing a new model (without using Quik-Plan), reload WORKPLAN.XLS and immediately save it with an appropriate new file name. Then proceed as outlined below.

Note that a user's assumptions are entered directly into Monthly, Quarterly & 4th/5th  Year Assumption Reports only. Output Reports contain extensive formulae which generate the projections. The only exceptions are the prior year P&L in the Monthly P&L and the opening balance sheet in Monthly Balance Sheet which must be entered directly into these reports.

Be sure to save the model at regular intervals.

		Initiating Keystrokes
Remove all existing assumptions		ALT+S, L
⇓
Enter basic data about the business, projection start date, currency and main products		ALT+S, B
⇓
Enter title for the new model		ALT+S, T
⇓
Enter monthly assumptions		ALT+A etc.
⇓
Enter opening balance sheet & prior year P&L		ALT+U, N ALT+U, M
⇓
Enter quarterly assumptions for second & third years		ALT+A, Q
⇓
Enter annual assumptions for 4th and 5th years (Pro & Super versions)		ALT+A, Y
⇓
Final recalculation		ALT+O, A
⇓
Save projections		ALT+F, S
⇓
Review output reports & graphs		ALT+U, ALT+C
⇓
Print reports & graphs		ALT+N, ALT+R


List of Toolbar Buttons - List of Toolbar Buttons
List of Toolbar Buttons

Note: To see a button's hint on the toolbar, slide the mouse's pointer slowly over the button.

See also:

Exl-Plan's Toolbar

Print reports within selected groups of reports
Print the current report

Go to Monthly Assumptions Report No. 1 - Sales & Finished Stock
Go to Monthly P&L Report
Go to 2nd & 3rd Year Quarterly Assumptions Report - Sales & Costs
Go to Quarterly P&L Report
Go to Annual P&L Report

Access graphs

Mark position of cursor
Jump back to the marked position of the cursor

Move backward one report
Move forward one report

Calculate entire model with error checking
Calculate current sheet without error checking
Go to Summary & Diagnosis Report

Clear unprotected values in assumption report row
Enter constant assumption value across remaining months/quarters
Enter incremented assumption value across remaining months/quarters
Enter assumption value changed by % across remaining months/quarters
Enter total assumption value & allocate equally across months/quarters
Allocate assumption across 12 months based on report's seasonal index

Unlock selected range
Lock selected range
Unprotect current report
Protect current report

Freeze panes within all reports

Restore default zoom for all reports
Zoom into current report by 10%
Zoom out  from current report by 10%


Creating a Plan within 15 Minutes - Creating a Plan within 15 Minutes
Creating a Plan within 15 Minutes

Exl-Plan incorporates a special facility, called Quik-Plan, for producing outline projections for three years (five years with Pro & Super versions) based on high-level assumptions entered into a special report (at the QUIK worksheet). It is ideally suited for producing first-cut projections or for compiling preliminary forecasts which can be fine-tuned subsequently via Exl-Plan's more detailed monthly and quarterly assumption reports.

For a full explanation of Quik-Plan refer to Using Quik-Plan. See also How Quik-Plan Handles Assumptions.

The procedure below summarises the key tasks involved in using Quik-Plan and very quickly producing multi-year projections as a Textual Summary Report.

Full Details:

Procedure for Using Quik-Plan


Creating a Plan within 15 Minutes - Creating a Plan within 15 Minutes - Procedure for Using Quik-Plan
Procedure for Using Quik-Plan

		Initiating Keystrokes
Enter basic data about the business, projection start date, currency and main products		ALT+S, B
⇓
Title the new model		ALT+S, T
⇓
Save the model as a new file		ALT+F, A
⇓
Use Quik-Plan		ALT+O, Q
⇓
Save projections		ALT+F, S
⇓
View or print reports & graphs		ALT+U, ALT+N, ALT+C
⇓
Review/refine assumptions		ALT+A
⇓
View/print Textual Summary Report		ALT+U, X ALT+N, X


Welcome to Exl-Plan - Welcome to Exl-Plan
Welcome to Exl-Plan

Exl-Plan is a powerful, easy-to-use software package for preparing comprehensive financial projections, budgets, business plans etc. for one, three or five years ahead. It can also be used as a tool for strategic and corporate planning, business restructuring, financial appraisals and performance monitoring within almost any size of company.

Exl-Plan is available in five versions and two editions using UK/International and US/Canadian accounting conventions. They all have the flexibility to handle manufacturing, distribution and service businesses and are suitable for established businesses as well as for new ventures and strategic business units within major corporations. Exl-Plan is also ideal for use by professional advisers, consultants, training organisations, financial institutions and enterprise support agencies.

Exl-Plan is primarily a workbook file which runs on the best selling Excel spreadsheet from Microsoft®. As Exl-Plan incorporates extensive formulae and pre-programmed menus and buttons only a very basic knowledge of Excel is required to prepare highly professional and presentable projections.  Because Exl-Plan is an open system, advanced Excel users can utilise their expertise to enhance and expand Exl-Plan to meet their particular needs (see Changing Exl-Plan).

Exl-Plan incorporates a comprehensive range of data entry and planning facilities and features. It is suitable for managers and business people with minimal previous experience of financial or business planning as well as for experienced planners, accountants, spreadsheeters and model-builders.

See also:

Powerful & Easy-to-Use

About Invest-Tech (author of Exl-Plan)


Powerful & Easy-to-Use

Exl-Plan is pre-formatted to handle the very wide range of the variables and functions normally encountered when preparing financial projections. Based on the monthly and quarterly assumptions entered by the user, it compiles detailed and fully integrated financial projections for one year on a monthly basis and, in less detail, for a further two years on a quarterly basis. It produces dozens of pro-forma financial and management reports together with numerous graphs for key variables. Exl-Plan contains numerous facilities for accelerating the process of preparing projections and for undertaking global or specific what-if sensitivity analyses alongside scenarios created using Excel's built-in Scenario Manager.

Exl-Plan is easy-to-use because all the formulae for financial calculations are pre-written; reports are pre-formatted and clearly laid out; menu structures follow logical sequences; and online help is available to the user at all stages. For experienced users of Excel, Exl-Plan offers a familiar environment which can be tailored and expanded to meet specialist needs and utilise existing spreadsheet-based data without the need to get involved in any extensive import/export procedures.

The time required to set-up and become familiar with Exl-Plan is less than an hour as compared with the many hundreds of hours that would be required to construct a comparable-quality financial model.

To get started, refer to Very Quick Start for fast-track guidance on installing Exl-Plan; exploring its main features and facilities; and developing an initial set of projections. Ultimately, the best way to learn about Exl-Plan is to start using it in a real situation by entering meaningful assumptions, compiling and reviewing projections, printing reports and graphs etc.

See also:

Welcome to Exl-Plan

About Invest-Tech (author of Exl-Plan)


Typographical Conventions

The following typographical conventions are used throughout this  help system:

Zzzz | Zzzzzz  indicates a menu option

ZZZZZZZ.XLS specifies a file name

ZZZZ refers to a worksheet name

"zzzz" refers to a toolbar button

This manual covers the UK/International edition of Exl-Plan and uses UK spellings and financial terms.

See also:

Help System

Very Quick Start

Introducing Exl-Plan


About Invest-Tech

Exl-Plan is the third-generation version of Invest-Tech's integrated financial planning systems. These have been developed over several years and are used by a wide variety of businesses ranging from start-ups to major companies.

Formed in 1983, Invest-Tech is an independent business and management consultancy based in Ireland and specialising in business planning & strategy, and the development of related software. It is a Registered Practice within the Institute of Management Consultants in Ireland (IMCI) and a Company Member of the Association of Shareware professionals (ASP) in the US.

Further information about Invest-Tech's other software products for business cashflow & financial planning and for personal planning along with access to downloadable shareware versions can be obtained from its PlanWare WWW site at the following URL:

www.planware.org

See also:

Support Policy

Model Development Services

Other Products

Contacting Invest-Tech

User Registration Form


Financial Modelling & Business Planning - Financial Modelling & Business Planning
Financial Modelling & Business Planning

This general introduction to financial modelling and business planning will be of particular interest to non-financial managers and first-time entrepreneurs.

The following topics are discussed:

♦	Computers & Modelling

♦	Uses of a Model

♦	Preparing to Plan

♦	Pitfalls & Dangers

♦	More about Business Planning


Computers & Modelling

By means of a computer and suitable software, a mathematical model of a business's finances can be constructed to help prepare its financial projections. A computer-based model reduces the tedium of carrying out numerous repetitive calculations and simplifies the alteration of assumptions and the presentation of results.

Financial models are used to compile forecasts and budgets; to assess possible funding requirements; and to explore the likely financial consequences of alternative funding, marketing or operational strategies.  They can also be used for business planning, investment or funding appraisals and for financial analysis.  Used effectively, a model can help prevent major planning errors, identify or evaluate opportunities, attract external funding, provide guidance, evaluate financial and development options, monitor progress etc.

A model utilises assumptions on sales volumes, prices, operating costs, funding etc., to produce projected balance sheets, profit & loss accounts and cashflow statements.

A model, like Exl-Plan, can handle most types of variables normally encountered in financial planning. It is the electronic equivalent of a book of reports. Each page (worksheet) contains variable descriptions in the left-hand column, titles along the top, and, in the middle, space for inputting hundreds of assumptions and holding thousands of formulae. The computer's screen serves as a small window on this electronic book as illustrated in the following simplified diagram.

Note that the left-hand column and upper title rows are frozen and stay in view when scrolling around the model.     a = assumptions     f = formulae

Because a model is fully integrated, resides in the computer's memory and all its assumption variables are linked by formulae, a change to any assumption will alter all dependent values throughout the model when it next recalculates. For example, a change to an interest rate in the third month of the first year will have an impact on the projections for the remaining months of that year and throughout the following years.

See also:

Uses of a Model

Preparing to Plan

Pitfalls & Dangers

More about Business Planning


Uses of a Model

Initial assumptions can be readily altered to evaluate alternative scenarios.  For example, a model could be used to:

♦	Explore the extent to which future sales can be increased while holding borrowings within predetermined limits.

♦	Assess the effects of varying selling prices and/or volumes on net profits.

♦	Determine the optimum level and mix of future funding for a business.

For the management of an existing business, or promoters planning a substantial new venture, financial modelling can be an invaluable tool to assist the preparation of a business plan. However, business planning should not be confused with the preparation of financial projections.  Sound planning should provide the basis for financial projections that can be derived arithmetically by a model.

Once basic issues relating to markets, sales and operations have been fully researched and reviewed, a model can be used to generate the financial projections.

See also:

Computers & Modelling

Preparing to Plan

Pitfalls & Dangers

More about Business Planning


Preparing to Plan

Before using a financial model to help plan the future of a business, a manager or entrepreneur should:

♦	Decide at the very outset on the central purpose of the modelling exercise (raise funds etc.); the target audience (co-directors, financial institutions etc.); and the time horizon (one year etc.).

♦	Identify and think through all the critical assumptions. Prepare outline projections to confirm their overall direction, examine the critical elements in detail and consider strategic issues relating to sales, profitability, funding etc. The Quik-Plan facility (see Using Quik-Plan) within Exl-Plan is ideal for preparing first-cut projections that can be progressively refined and expanded.

♦	Check that all key assumptions (e.g. sales forecasts) and data (e.g. opening balance sheet and any prior-year financial results) are to hand and have been adequately researched.

♦	Recognise the danger of presenting too much detail or too many reports. Most senior managers, investors and financiers seek simple financial statements, which they can readily see to have been based on detailed analysis and realistic assumptions. Detail can be provided on request or relegated to appendices in a report or business plan. Make use of Exl-Plan's summary reports, including its Textual Summary Report, and numerous graphs to present the highlights of a plan or projections.

See also:

Computers & Modelling

Uses of a Model

Pitfalls & Dangers

More about Business Planning


Pitfalls & Dangers

The veracity and usefulness of these projections will be completely determined by the quality and reliability of the underlying assumptions. For example, if sales or cost forecasts are unrealistic or inadequately researched, then the value of a model's output is greatly diminished.

When preparing financial projections, be conscious of the following pitfalls and dangers:

♦	Using financial forecasting as a substitute for business planning

♦	Ignoring historic trends at company, sectoral and national levels

♦	Overstating market shares and growth, sales forecasts,  and profit levels

♦	Underestimating costs and delays likely to be encountered

♦	Disregarding industry performance norms and competitors' responses

♦	Breaching generally-accepted financial guide lines and ratios

♦	Making unduly optimistic assumptions about the availability of loans, trade credit, grants, equity etc.

♦	Seeking spurious accuracy while ignoring matters of strategic importance.

These problems can arise as the result of a lack of foresight or insight, or due to excessive optimism. Because they can lead to under-estimation of the resources required to develop a business with potentially disastrous consequences, it can be completely counterproductive to overstate the potential of a business.

Realistic views should always be taken of a business's prospects, prospective profits, funding requirements etc.  There is often merit in compiling worst case projections to complement most likely or best forecasts. Exl-Plan's sensitivity-analysis facilities (See Sensitivity Analysis) and Excel's Scenario Manager can be used for this purpose to temporarily override the basic assumptions.

See also:

Computers & Modelling

Uses of a Model

Preparing to Plan

More about Business Planning


More about Business Planning

Preparing a set of projections is only a means to an end. Once plans or projections have been approved or in the course of being implemented, they should be regularly updated and compared with the results being achieved.

A plan is only useful if it is being adhered to, it serves as a benchmark for control purposes, and its projected outcomes are being realised.

While a clear business plan with sound projections cannot guarantee success, the absence of a plan or poor projections could ensure the eventual failure of a business.

Users with limited financial expertise are urged to work closely with their professional advisers when using Exl-Plan.

An expanded version of this topic is available at Invest-Tech's PlanWare site on the Internet at:

www.planware.org

This also contains other informative papers dealing with business plan preparation, strategic planning, cashflow forecasting, managing working capital, business idea generation etc.

See also:

Computers & Modelling

Uses of a Model

Preparing to Plan

Pitfalls & Dangers


Introducing Exl-Plan - Introducing Exl-Plan
Introducing Exl-Plan

This general appreciation of Exl-Plan covers the following topics:

♦	Versions of Exl-Plan

♦	Scope of Exl-Plan

♦	Facilities & Features of Exl-Plan

♦	Assumptions Handled by Exl-Plan

Exl-Plan is an advanced spreadsheet-based financial modelling system for preparing comprehensive financial projections, funding applications, budgets, business plans etc. for up to three/five years ahead. It can also be used for strategic planning, financial appraisals and performance monitoring. It can handle any type of business, with annual sales ranging from about £/$ 50,000 up to £/$ 100 million, engaged in manufacturing, distribution or services.

Based on a user's assumptions, Exl-Plan compiles integrated financial projections (P&Ls, cashflows, balance sheets & ratios) for one year ahead on a monthly basis and, in less detail, for a further two/four years. It produces numerous management and financial reports along with graphs of key variables that can be displayed on the screen or printed.


Versions of Exl-Plan

Five primary versions of Exl-Plan are available as follows:

Free for new and established businesses of all sizes seeking basic six-month profitability and cashflow projections. It is distributed as freeware..

Micro for new & very small companies, sole traders or partnerships. Twelve-month projections for first year. Makes projections for the second and third years on a quarterly basis. Distributed as shareware and commercial software.

Lite for new & small to medium-sized companies, sole traders or partnerships. Twelve-month projections for first year. Projects for the second and third years on a quarterly basis. Distributed as shareware and commercial software.

Pro for new & medium to large companies. Twelve-month projections for first year. Projects for the second and third years on a quarterly basis and for fourth/fifth years on an annual basis. Distributed as shareware and commercial software.

Super for new & substantial companies. Twelve-month projections for first year. Projects for the second and third years on a quarterly basis and for fourth/fifth years on an annual basis. More detailed than Pro version with additional planning tools. Distributed as shareware and commercial software.

The Free, Micro, Lite, Pro & Super versions of Exl-Plan are each available using either UK/International or US/Canadian accounting formats and terminologies. Different currencies symbols and denominations (units, 000s, millions or billions) can be used.

Exl-Plan will run with Excel for Windows releases 5, 7, 8, 95, 97, 2000 or higher. Files created with the commercial version of Exl-Plan  are compatible upwards and downwards with all these releases provided that the user is licensed to use Exl-Plan on more than one PC. The commercial license for Exl-Plan restricts the use of Exl-Plan to a single PC or network node. The only exception applies where a registered person uses a registered copy of Exl-Plan on a desktop PC and, exclusively, on their own Notebook computer.

Whilst most references Help System refer to Exl-Plan Super, instructions can be readily applied to suit the other, simpler versions. A user wishing to switch to a different version of Exl-Plan should contact Invest-Tech for details of upgrade charges etc.

See also:

Scope of Exl-Plan

Facilities & Features of Exl-Plan

Assumptions Handled by Exl-Plan


Scope of Exl-Plan

Exl-Plan comprises several integrated sub-models residing within the main model and covering a three/five year forecasting period as follows:

♦	Detailed assumptions and projections for the initial 12 months

♦	Annualised projections for the first year based on the 12-month projections

♦	Less detailed assumptions and projections for the initial 12 quarters along with projections for the first four quarters derived automatically from the 12-month projections

♦	Annual projections for three years based on the 12-quarter projections.

♦	Projections for a further two years based on simplified annual assumptions (Pro & Super versions).

Projected profit & loss accounts, cashflows, balance sheets and ratio analyses are generated for each month, quarter and year. There is an opening balance sheet and a summary profit & loss account for the year preceding the start of the projections. For example, if projections start in April 2000, a P&L (estimated or actual) for the year ending March 2000 can be entered for reference and comparison purposes.

Output from Exl-Plan can be printed or viewed on a computer screen. This includes the following:

♦	Over thirty pro-forma reports containing assumptions, profit and loss accounts, cashflow projections, balance sheets, performance reviews and summaries.  These are detailed in  Lists of Reports

♦	Numerous graphs showing key projections, ratios etc. as detailed in Lists of  Graphs.

♦	A twelve-page Textual Summary Report containing a summary of the projections in textual form with embedded values and tables (*Output | Textual).

See also:

Versions of Exl-Plan

Facilities & Features of Exl-Plan

Assumptions Handled by Exl-Plan


Facilities & Features of Exl-Plan

Once loaded, Exl-Plan automatically extends Excel's menu system and adds its own toolbar.

Exl-Plan contains dozens of pre-formatted and protected worksheets incorporating thousands of formulae, assumption values etc. Its facilities and features include the following:

♦	Quik-Plan to enable a user produce first-cut projections for three (five for Pro & Super) years quickly (i.e. within about 15 minutes). Refer to  Using Quik-Plan.

♦	Comprehensive set of tailored toolbar buttons. Refer  Exl-Plan's Toolbar and List of Toolbar Buttons .

♦	Facilities for inserting constant and varying monthly values and for entering assumptions based on seasonal patterns etc. specified by the user. Refer to Fast Entry of Assumptions.

♦	Selective what-if or sensitivity analysis covering months and quarters for the three (five) years. Refer to  Guidance on Entering Assumptions.

♦	Sensitivity analysis tool for globally changing selling prices, volumes, direct costs and overheads for months and quarters. Refer to Sensitivity Analysis.

♦	Automatically generated 'what-if' tables which display the impact of incremental changes in sales volumes, selling prices, cost of sales and overhead costs on projected profitability for the first year. Refer to Viewing What-If Analyses.

♦	Profitability chart based on the first-year projections which also allows a user to explore the impact of specific changes in key variables and assumptions on overall profitability. Refer to Planning Better Profitability.

♦	Initial twelve-week cashflow forecasts derived from monthly cashflow projections for the first year. Refer to Making Short-Term Cashflow Projections.

♦	Facility to produce updated first-year projections of sales and profitability by combining year-to-date actuals with projections for remaining months of the year. Refer to Tracking Trading Performance.

♦	Tables to analyse longer term trends (five years historical plus five-years projected) for key ratios and performance measures. Refer to Analysing Long-Term Trends.

♦	Book-marking facility to allow a user mark a location within a worksheet and to return quickly to it.

♦	Extensive on-line help - refer to  Help System for further details.

♦	Multiple safeguards to protect formulae etc. and help prevent accidental changes being made to worksheets. Refer to Protecting Your Work.

♦	Full access to worksheets and formulae to facilitate customisation and expansion. Refer to Changing Exl-Plan.

♦	Error trapping to advise when certain types of calculation errors arise. Refer to Avoiding Calculation Errors and to Calculation Error Messages.

See also:

Versions of Exl-Plan

Scope of Exl-Plan

Assumptions Handled by Exl-Plan


Assumptions Handled by Exl-Plan

Exl-Plan handles a very wide variety of assumption variables and financial functions. Many of these can be expanded or altered by the user as explained in Changing Exl-Plan in order to embrace existing spreadsheet-based data or to accommodate special user requirements.

These assumptions can be grouped as follows:

 * Sales, costs, stock & overhead assumptions

 * Fixed assets, investment & financing assumptions

 * Value Added Tax & credit assumptions

See also:

Versions of Exl-Plan

Scope of Exl-Plan

Facilities & Features of Exl-Plan

Guidance on Entering Assumptions


Sales, Cost, Stock & Overhead Assumptions

The range of sales, cost, stock and overheads assumptions handled by Exl-Plan Super include:

♦	Monthly sales forecasts for thirty product/market subgroups within six main sales groups which can be defined by the user. There are facilities for forecasting either sales volumes & prices or sales values.

♦	Facilities for specifying the cost of materials/goods in percentage or absolute terms for the six main sales groups; for setting finished stock levels (linked by formulae to sales for the current or future months); and for setting material stock targets for each main group.

♦	Direct labour manpower can be specified for each main sales group. There are eight other user-definable direct/variable cost categories for which separate credit terms can be set.

♦	Forty user-definable categories of overhead expense for each of which credit terms can be specified.

See also:

Fixed assets, investment & financing assumptions

Value Added Tax & credit assumptions

Versions of Exl-Plan

Scope of Exl-Plan

Facilities & Features of Exl-Plan

Guidance on Entering Assumptions


Fixed Asset, Investment & Financing Assumptions

The range of fixed asset, investment and financing assumptions handled by Exl-Plan Super include:

♦	Four categories of fixed assets with provision for handling depreciation (based on original cost or declining balance), finance leases, capital grants and additions/disposals for each category of asset.

♦	Separate interest rates for cash balances, overdraft and long-term debt.

♦	Facilities for increasing and decreasing long-term debt; assigning it between short- and long-term liabilities; and for making loan repayments inclusive or exclusive of interest.

♦	Share issues, dividend declarations and payments, corporation tax provisions and payments, and revenue grants.

See also:

Sales, costs, stock & overhead assumptions

Value Added Tax & credit assumptions

Versions of Exl-Plan

Scope of Exl-Plan

Facilities & Features of Exl-Plan

Guidance on Entering Assumptions


Value Added Tax & Credit Assumptions

The range of Value Added Tax and credit assumptions handled by Exl-Plan Super include:

♦	Different VAT rates can be set for the six main sales groups.

♦	Facilities for setting different VAT rates for inputs & capital expenditure; for specifying VAT payment months; and for paying VAT on an invoice or cash-received basis.

♦	Provision for setting different credit terms (up to 6 months ahead) for the six main sales groups and their related purchases (materials or goods for resale).

♦	Facilities to defer the cash payments relating to many categories of monthly expense, capital expenditure, grant receipts, dividends etc.

See also:

Sales, costs, stock & overhead assumptions

Fixed assets, investment & financing assumptions

Versions of Exl-Plan

Scope of Exl-Plan

Facilities & Features of Exl-Plan

Guidance on Entering Assumptions


Loading Exl-Plan for First Time - Loading Exl-Plan for First Time
Loading Exl-Plan for First Time

These procedures explain how to install and load Exl-Plan for the first time with Windows 3.1 and Windows 95/98/NT. They should be followed by users who have not completed Very Quick Start .

New users should complete and return the User Registration Form immediately upon receiving Exl-Plan in order to qualify for support etc.

If Exl-Plan was secured as shareware and subsequently upgraded remotely via Invest-Tech, the upgraded Exl-Plan workbook file should be kept safe and intact as the primary master version of Exl-Plan. Copies of this file should be created and used for normal working purposes.

To uninstall Exl-Plan, select Uninstall within the Exl-Plan Application Group if using Windows 3.1. For W95/98/NT, press Start and then select Settings, Control Panel, Add/Remove Programs, and Exl-Plan.

When being loaded for the first time, Exl-Plan needs 8 Mb of disk space to enable it to automatically create backup copies (WORKPLAN.XLS and TESTPLAN.XLS) during set up. If this space is not available, Exl-Plan will skip their creation.

Installation takes up to five minutes, depending on the speed of the PC being used. Exl-Plan must always be installed from supplied installation disk(s) or via a downloaded installation file. It will not operate correctly if it is installed by simply transferring an Exl-Plan workbook file from one PC to another.

Full Details:

Installation Procedure for Windows 3.1

Installation Procedure for Windows 95/98/NT


Installation Procedure for Windows 3.1

1.	Close all other applications.

2.	Confirm that at least 8 Mb of space is free on the hard disk to which Exl-Plan will be installed.

3.	Insert the Exl-Plan Product Disk in a disk drive and use the Run option within File Manager or Program Manager to run the executable file EXLPLAN.EXE by entering A:\EXLPLAN.EXE at the prompt (replace A: by B: if appropriate). Follow on-screen instructions to install Exl-Plan's files into the appropriate sub-directories.
(If Exl-Plan was acquired by downloading from the Internet to a temporary sub-directory, the downloaded executable file should be run and its on-screen instructions followed to install Exl-Plan's files into the appropriate sub-directories.)

4.	Once installation is complete, go to the Exl-Plan Application Group and review any displayed README file.

5.	Load Excel and open the main Exl-Plan workbook file EXLPLAN.XLS located in the sub-directory specified during installation. Once loaded, follow on-screen prompts and then wait a few moments while copies of Exl-Plan are created. These copies, WORKPLAN.XLS and TESTPLAN.XLS, should be used as the primary working copy and for experimentation respectively. EXLPLAN.XLS should be carefully retained as the original master copy.

Note: If Exl-Plan failed to detect the correct size of the screen display being used, use *Setup | Change Default Zoom Value.

This completes installation of Exl-Plan. If desired, close TESTPLAN.XLS and exit Excel, or proceed immediately to Getting Started with Exl-Plan.


Installation Procedure for Windows 95/98/NT

1.	Close all other applications.

2.	Confirm that at least 8 Mb of space is free on the hard disk to which Exl-Plan will be installed.

3.	Insert the Exl-Plan Product Disk in a disk drive and use Start | Run to run the executable file EXLPLAN.EXE by entering A:\EXLPLAN.EXE at the Open prompt (replace A: by B: if appropriate). Follow on-screen instructions to install Exl-Plan's files into the appropriate folders.
(If Exl-Plan was acquired by downloading from the Internet to a temporary folder, the downloaded executable file should be run and its on-screen instructions followed to install Exl-Plan's files into the appropriate folders.)

4.	Once installation is complete, go to the Exl-Plan menu within the Programs Menu and review any indicated README file.

5.	Load Excel and open the main Exl-Plan workbook file EXLPLAN.XLS located in the folder specified during installation. Once loaded, follow on-screen prompts and then wait a few moments while copies of Exl-Plan are created. These copies, WORKPLAN.XLS and TESTPLAN.XLS, should be used as the primary working copy and for experimentation respectively. EXLPLAN.XLS should be carefully retained as the original master copy.

NB: When Exl-Plan starts loading, Excel may display a dialog about macros and viruses. If Exl-Plan was secured from a reliable source or directly from Invest-Tech, press the Enable Macros button. Note that Exl-Plan will not load properly if the Disable Macros button is pressed. As loading progresses, a further dialog may appear about the location of macros. This can be disabled or ignored.

Note: If Exl-Plan failed to detect the correct size of the screen display being used, use *Setup | Change Default Zoom Value.

This completes installation of Exl-Plan. If desired, close TESTPLAN.XLS and exit Excel, or proceed immediately to Getting Started with Exl-Plan.


Getting Started with Exl-Plan - Getting Started with Exl-Plan
Getting Started with Exl-Plan

The following basic exercises introduce Exl-Plan's main features and facilities:

Familiarisation - Part 1

Familiarisation - Part 2.

See also:

♦	Protection of cells, sheets etc.

♦	Printing

♦	Zoom settings

♦	Fast entry of assumptions

♦	Exl-Plan's toolbar

♦	Freezing titles.

New users: Don't forget to complete and return the User Registration Form to qualify for support etc.

When running Exl-Plan with W95/98/NT, it is best to place the taskbar at the bottom of the screen in order to improve the view of reports etc.


Familiarisation - Part 1

1.	Reload Excel with TESTPLAN.XLS. This file contains specimen assumptions that can be used for experimentation etc. and, being a copy of the original, its values etc. can be readily altered.

2.	Take the Quik-Tour (*Exl-Plan Help | Quik-Tour) to get an overview of Exl-Plan and its facilities and layout.

3.	Run the cursor across the menu bar - File, Edit etc. and note that Exl-Plan has added several new menus (*Assumptions, *Protection etc.).

4.	Click the M_S worksheet tab (or the "MA" toolbar button) to go to the monthly assumptions report for sales forecasts etc. Note that assumption values (shown in blue) should be entered or altered only within the rectangular boxed-in areas opposite the ->> or <<->> arrows. Values shown in black are formulae and are normally locked (protected).

5.	Try adding or changing some of the supplied assumption values for sales and recalculate (*Tools | Calculate All & Check) to check the model's integrity and to update all the monthly, first-year, quarterly and annual output reports. (Note that Exl-Plan always uses manual calculation).

6.	Use the *Assumptions menu, worksheet tabs and toolbar buttons (especially "<-" and "-.>") to view other monthly and quarterly assumption reports. Change assumption values as desired and periodically recalculate with the *Tools | Calculate All & Check menu command to utilise Exl-Plan's error-trapping facilities.


Familiarisation - Part 2

1.	Check the Summary & Diagnosis Report (*Output | Summary) and Textual Summary Report (*Output | Textual) or click the SUM and TEXT worksheet tabs to see the consequences of changing assumptions. Note that monthly assumption reports contain calculated values as well as assumption values. These values feed into the monthly output reports (P&Ls, cashflows etc.) which are summarised in the first-year output reports located immediately to the right of the monthly reports.

2.	Visit the first of two Quarterly Assumptions Reports (*Assumptions | Quarterly Assumptions - Sales/Costs, or press the "QA" button, or click the Q_A1 tab). This includes monthly assumptions for the first year converted to quarterly values as well as provision for entering quarterly assumptions for the second and third years. These feed into the quarterly output reports that are summarised in the annual output reports.

3.	In the Pro & Super versions of Exl-Plan, high-level annual assumptions are used to produce projections for the fourth and fifth years (*Assumptions | 4-5th Year Assumptions, or press the "45" button, or click the 45_A tab).

4.	Experiment with Exl-Plan's facilities, within the Assumptions menu for entering series of assumption values within rows. These can be accessed using toolbar buttons (refer to  Fast Entry of Assumptions). They enable a user to:

♦	Enter a constant monthly or quarterly assumption

♦	Enter a value which is increased either arithmetically, geometrically, seasonally or pro-rata

♦	Clear a row of assumption values.

5.	Use the "M" and "J" toolbar buttons to mark the cursor's location, visit other locations and then jump back to the marked location.

6.	Use menus, worksheet tabs or buttons (on certain sheets and on the toolbar) to view output reports, charts etc.

7.	Use Exl-Plan's *Print menus, or buttons to print assumption and output reports.

8.	Experiment with the sensitivity-analysis facility in the SENS worksheet (*Tools | Sensitivity Analysis) by entering percentage changes for key variables and recalculating. Note that these changes temporarily over-ride existing assumptions within Exl-Plan. View their effects in the Textual Summary Report (TEXT worksheet) and in the charts. Press the "Zeroise Factors" button to revert to the original projections. See Sensitivity Analysis for a more detailed description of the sensitivity-analysis facility.


Protecting Your Work

To minimise the possibility of corruption of formulae, without restricting potential for expansion or tailoring, Exl-Plan contains extensive safeguards to protect and lock ranges, worksheets and workbooks. These can be removed by experienced users.

Users should never alter Exl-Plan's toolbar, buttons, dialog boxes, macro modules, range names or worksheet names. If these are altered in any way, either by accident or design, Invest-Tech cannot accept any responsibility for support or give support in the restoration of a modified file. It is the responsibility of the user to ensure that a fully intact copy of Exl-Plan is always available as a backup or fall back.

Do not rename an Exl-Plan file using FileMgr or Explorer as  this will cause the file to 'lose' all references to its toolbar. Instead, use Excel to load the file and then save it with the desired new name.

The best way to protect a model is to:

1.	Make regular use of Exl-Plan's error-trapping facilities (*Tools | Calculate & Check or the "C" button) to check for errors connected with formulae and data entry - see Avoiding Calculation Errors for further information.

2.	Make frequent back-up copies of error-free files.

To minimise possible disruption to formulae etc., Exl-Plan contains the following protection facilities:

♦	When Exl-Plan loads, all its worksheets are automatically protected.

♦	Exl-Plan's facilities for entering rows of assumption values will not operate if initiated while the cursor is located at a locked cell.

♦	As supplied, all cells within Exl-Plan are locked with the exception of the unlocked values located inside the rectangular boxed-in areas assigned to assumption values within assumption reports.

♦
Selected cell ranges can be locked or unlocked using the options within the *Protection menu or the corresponding buttons on the toolbar. Locked cells cannot be changed and their values are black coloured. Unlocked cells are coloured blue whenever Exl-Plan facilities for unlocking cells are used.

♦	The *Protection menu includes options for protecting individual sheets or for protecting all worksheets simultaneously.

♦	The *Setup | Clear All Assumption Reports or Clear Current Assumption Report menu options only clear unlocked assumption values within Exl-Plan's assumption reports (as well as unlocked cells in columns used for the prior year P&L in the Monthly Profit & Loss Accounts and opening balance sheet in the Monthly Balance Sheets). These facilities ignore protected assumptions within assumption reports as well as all locked and unlocked formulae and text within these reports. As this approach could lead to a failure to remove all assumptions, Exl-Plan automatically checks whether all assumptions have been successfully removed. If it issues an advisory message, systematically check all assumption reports for formulae and text within assumption areas.

In theory, either Excel's or Exl-Plan's workbook protection facilities can be used to prevent accidental changes to formulae. Unfortunately, this also restricts the functioning of Excel's and Exl-Plan's menus and leads to error messages etc. when invoking various menu options or buttons. In practice, the *Protection | Protect All Worksheets menu option is the most satisfactory method of protecting Exl-Plan.

See also the following basic topics:

Printing

Zoom settings

Fast entry of assumptions

Exl-Plan's toolbar

Freezing titles


Printing

Exl-Plan's *Print menus and its print-related toolbar buttons allow users to group reports for batch printing as well as to print individual reports.

The default orientation is portrait for most of Exl-Plan's reports. Usually, it is more satisfactory to print reports with this orientation and, if necessary, to subsequently enlarge them using a photocopier rather than to print with landscape (with Page Setup Scaling set to Fit to 1 page wide) and then have to assemble and duplicate reports dispersed over several sheets.

Page Setup and Printer Setup can be altered to a considerable extent by users. For example, the paper size can be changed for individual reports or groups of reports.

Exl-Plan's boxed-in assumption areas are formatted in a cream(ish) colour to aid identification etc. If, when printing assumption reports, the printer indicates that it has run out of memory, the simplest solution is to remove this colouring from all the boxed-in areas within assumption reports. If, in other circumstances, the printer runs out of memory, break up the 'problem' report by inserting a page break at an appropriate row.

When viewing reports on screen or in print, ###### symbols signify a cell overflow due to the large numbers. Only consider widening columns as a last resort because of the adverse impact on the width of printed reports. Instead, try the following options first:

♦	Increase the zoom value for the report in the expectation that the cell overflows only arise on the screen and not in printed reports.

♦	Reduce the number of decimal places displayed.

♦	Remove any bold formatting from the 'problem' numbers.

♦	Change number formatting to remove the comma separator (as in 1,000,000).

There is an option in the *Setup menu to specify whether empty rows (containing values totalling to zero) are printed within output (but not assumption) reports. This is very effective at suppressing unused variables. This facility complements Excel's outlining facilities that can be used to hide detailed rows within assumption reports. This facility does not operate if a report is being printed via the 'Print this sheet' toolbar button.

See also the following basic topics:

Protecting your work

Zoom settings

Fast entry of assumptions

Exl-Plan's toolbar

Freezing titles


Zoom Settings

As Exl-Plan loads, it detects the size of display being used and automatically adjusts Excel's custom zoom (*View | Zoom Custom Magnification) to display each of Exl-Plan's reports with the optimal magnification. This is based on the following display widths:

Exl-Plan's initial zoom value can be restored at any stage using *Setup | Revert to Default Zoom Value. The default zoom value can be changed via *Setup | Change Default Zoom Value.

The *Setup menu and toolbar both offer facilities to zoom into and out from an active Exl-Plan worksheet in increments of 10%.

See also the following basic topics:

Protecting your work

Printing

Fast entry of assumptions

Exl-Plan's toolbar

Freezing titles


Fast Entry of Assumptions

To complement Excel's facilities for entering data series, Exl-Plan also includes facilities (accessible via the *Assumptions menu and toolbar buttons) for fast entry of assumption values across rows as follows:

♦	Clearing a row of assumptions.

♦	Entering a constant value across a row.

♦	Entering an initial value and incrementing it by a specified fixed value.

♦	Entering an initial value and incrementing it by a specified percentage.

♦	Entering a single value and allocating it in equal amounts across the remaining periods.

♦	Entering a single value and having it allocated based on a seasonal index across all periods. To use this facility, the cursor must be located in the first monthly column within Monthly Assumption Reports Nos. 1 to 4.

These facilities can only operate inside the boxed-in rectangular assumption areas within the monthly and quarterly assumption reports.

See also the following basic topics:

Protecting your work

Printing

Zoom settings

Exl-Plan's toolbar

Freezing titles


Exl-Plan's Toolbar

When Exl-Plan loads, it automatically loads its own toolbar along with Excel's standard bar. This may cause the message "Exl-Plan is resetting toolbars, please wait a minute or so" to display during loading.

If all Exl-Plan's toolbar buttons cannot be displayed across the top of the screen (due to the screen display), the toolbar can be dragged onto the worksheet to become a rectangular-shaped floating toolbar.

The buttons on Exl-Plan's toolbar should never be modified or re-positioned. Do not rename an Exl-Plan file using FileMgr or Explorer as  this will cause the file to 'lose' all references to its toolbar. Instead, use Excel to load the file and then save it with the desired new name.

See List of Toolbar Buttons  for details. To see a button's hint, slide the mouse pointer slowly over the button.

If more than one copy of Exl-Plan is loaded at any one time, there will be a delay during loading while Exl-Plan's toolbar is reset for the second copy. Once loaded, the toolbars will automatically maintain their focus on the active window. If, for any reason, the Exl-Plan toolbar does not appear when accessing a report, use *Setup | Reset Toolbar Buttons.

If a copy of Exl-Plan is loaded, after having run a copy of Exl-Plan with the same file name but located within a different folder, the tool bar buttons may not work. The solution is to save the loaded file with a new name, close it and then reload it.

If Exl-Plan 'hangs' or freezes during loading while trying to reset its toolbar, refer to the accompanying README.DOC file for directions.

See also the following basic topics:

Protecting your work

Printing

Zoom settings

Fast entry of assumptions

Freezing titles


Freezing Titles

The Freeze Report Titles option within the *Setup menu (also accessible via a toolbar button) is a handy way to simultaneously to lock the tiles and variable descriptions for each report and to automatically move the cursor to the top left corner of each worksheet.

See also the following basic topics:

Protecting your work

Printing

Zoom settings

Fast entry of assumptions

Exl-Plan's toolbar


Using Exl-Plan - Using Exl-Plan
Using Exl-Plan

Exl-Plan is licensed for use on one computer or at one network node belonging to the organisation/individual named on the front worksheet. However, a registered user is permitted to use a single registered copy of Exl-Plan on a desktop PC and, exclusively, on their own Notebook computer.

Licences for multiple copies of Exl-Plan are available from Invest-Tech. DO NOT copy Exl-Plan for unlicensed users or sites, associated organisations, clients, customers, etc. This is expressly prohibited and could result in prosecution.  DO NOT alter the contents of the front worksheet or modules under any circumstances. Use of Exl-Plan indicates acceptance of these terms and conditions. For details, see Licence Agreement .

The topics below are relevant to all new users. They offer general advice on using Exl-Plan and cover the following:

1. Erasing Existing Values

2. Gathering & Entering Assumptions

3. Structuring a Model

4. Adapting Exl-Plan to Different Business Types

5. Using Quik-Plan

6. Model-Building Procedure

7. Guidance on Entering Assumptions

8. Avoiding Calculation Errors

9. Calculation Error Messages

10. Doing Sensitivity Analyses

11. Viewing 'What-If' Analyses

12. Planning Better Profitability

13. Making Short-Term Cash Projections

14. Tracking Trading Performance

15. Analysing Long-Term Performance

16. Making Simple Changes

17. Making Complex Changes

18. Extending Exl-Plan


Starting to Use Exl-Plan

The topics below explain how to erase existing values from Exl-Plan and approaches to gathering assumptions for inclusion in Exl-Plan:

 * Erasing Existing Values

 * Gathering & Entering Assumptions

See also:

Structuring a Model

Adapting Exl-Plan to Different Business Types

Using Quik-Plan

Model-Building Procedure

Guidance on Entering Assumptions

Avoiding Calculation Errors

Calculation Error Messages

Doing Sensitivity Analyses

Viewing 'What-If' Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Tracking Trading Performance

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Erasing Existing Values

Rows not needed in Assumption Reports MUST NOT be deleted.  Instead, they should be left empty or with zero values. Depending on Excel's set-up, zeros may appear as empty spaces. Note that Exl-Plan offers the option to suppress the printing of unused rows in output reports (*Setup | No Empty Rows Printed). Use can also be made of Excel's outlining facilities to hide unused rows within Exl-Plan.

To ensure that formulae etc. are not accidentally erased, regularly check that *Protection | Protect All Worksheets is enabled. To remove protection from a single report, use *Protection | Unprotect This Worksheet or its corresponding button. To remove protection from an assumption range, use *Protection | Unlock Range or its corresponding button.

To erase assumption values within an unlocked range (blue numbers within the rectangular areas bounded by lines), use the DEL key, or select the range of assumptions to be deleted and use either *Assumptions | Clear Assumptions or the corresponding unlock button.

To simultaneously erase all monthly and/or quarterly assumptions, use the *Setup | Clear All Assumption Reports or Clear Current Assumption Report menu commands.  Note that these commands ignore (a) locked assumption values within assumption reports, (b) formulae(either locked or unlocked) within these reports and (c) text (either locked or unlocked) within these reports. As this approach could lead to a failure to clear all assumptions, Exl-Plan automatically tests whether all assumptions have been successfully removed.

At this point, it may be worthwhile creating a copy of Exl-Plan with its assumptions removed as a template file (*.XLT) in the Exl-Plan subdirectory (with Windows 3.1). If using Excel 7 or higher with Windows 95/98/NT, save the template in Microsoft Office's Templates\Spreadsheet Solutions folder. Refer to Excel's Online Help for guidance on creating and using templates.

See also:

Gathering & Entering Assumptions

Structuring a Model

Adapting Exl-Plan to Different Business Types


Gathering & Entering Assumptions

Note that a user's assumptions are entered directly into Monthly, Quarterly & 4th/5th  Year Assumption Reports only. Output Reports contain extensive formulae which generate the projections. The only exceptions are the prior year P&L in the Monthly P&L and the opening balance sheet in Monthly Balance Sheet which must be entered directly into these reports.

When starting to prepare a new set of financial projections for a business, there are likely to be some significant gaps in the information needed to plan effectively.  It may be necessary to review historic accounts and budgets to derive basic cost or sales data; to conduct research and analyses of markets; to identify alternative strategies or scenarios; to make manpower projections; to consider funding issues etc.

Exl-Plan can assist this process by helping to pose the right questions and providing feedback on the financial implications of possible outcomes. However, bear in mind that, ultimately, Exl-Plan is only a high-powered calculating and printing system and that its output will only be as sound as the underlying assumptions. Refer to Pitfalls & Dangers for advice on preparing projections.

On the first occasion that Exl-Plan is used to produce real projections for a business, curtail the detail of monthly assumptions relating to sales, expenses etc. and concentrate initially on producing summarised monthly profit & loss accounts for the first year.

For example, start by entering just single rows of assumptions for sales and use only a few rows for high-level direct and overhead expenses etc.  As all other sales, cost and overhead assumptions will have been previously set to zero, the range of assumptions will be manageable and it should be straight-forward to follow these through the various formulae and reports. Review the initial results and then progressively add greater detail.

A printout of Monthly Assumption Reports with all assumptions erased can be a very useful aid to collecting assumption data.

See also:

Structuring a Model

Adapting Exl-Plan to Different Business Types

Using Quik-Plan

Model-Building Procedure

Guidance on Entering Assumptions

Avoiding Calculation Errors


Structuring a Model

Exl-Plan can be structured to suit manufacturing, distribution or service businesses. Guidelines on using Exl-Plan to suit different types of businesses are contained in Adapting Exl-Plan to Different Business Types.

For new users, the best approach is to start using Exl-Plan exactly as supplied and then to make progressive changes according as experience is acquired and needs become clearer. Refer to Making Simple Changes for guidance on making very simple but highly effective modifications to Exl-Plan.

For most users, it is not practicable to alter the layout of Exl-Plan's reports by moving or radically changing rows etc. Instead, where special reports are required for presentation purposes or to conform to in-house layouts, the simplest solution is to create separate specially formatted reports. See Changing Report Layouts for detailed guidance.

Provided that their meanings remain consistent with the formulae built into Exl-Plan, variable descriptions in column A of many worksheet reports can be readily changed to suit a particular business's terminology.

If substantial changes must be made to Exl-Plan and only limited spreadsheet expertise is available, it may be desirable to contact Invest-Tech about undertaking this work - see Model Development Services.

See also:

Adapting Exl-Plan to Different Business Types

Using Quik-Plan

Model-Building Procedure

Guidance on Entering Assumptions

Avoiding Calculation Errors

Making Simple Changes


Using Quik-Plan

Quik-Plan is an unique facility within Exl-Plan for (a) producing simple quick and dirty projections for three years (five years with Pro & Super  versions of Exl-Plan) or (b) generating first-cut forecasts which can be refined and fine-tuned from within the Monthly and Quarterly Assumption Reports.

The entire process of producing financial projections is remarkably fast if Quik-Plan is used to generate the projections and these are subsequently printed in summary form via the Textual Summary Report. This approach could be particularly useful to professional advisers, banks and other organisations providing front-line support to new and small enterprises.

Full Details:

Outline Procedure for using Quik-Plan

Procedure for setting up Exl-Plan prior to using Quik-Plan

Procedure for Using Quik-Plan


Outline Procedure for using Quik-Plan

		Initiating Keystrokes
Enter basic data about the business, projection start date, currency and main products		ALT+S, B
⇓
Title the new model		ALT+S, T
⇓
Save the model as a new file		ALT+F, A
⇓
Use Quik-Plan		ALT+O, Q
⇓
Save projections		ALT+F, S
⇓
View or print reports & graphs		ALT+U, ALT+N, ALT+C
⇓
Review/revise assumptions		ALT+A
⇓
Print Textual Summary Report		ALT+N, X


Procedure for setting up Exl-Plan prior to using Quik-Plan

To get started with Quik-Plan, load Excel and open an existing Exl-Plan file (e.g. a copy of WORKPLAN.XLS). Immediately, save this file with a new file name and then undertake the following preparatory tasks:

1.	Enter basic information about the business via the Basic Information dialog (*Setup | Enter Basic Model Info) as follows:

♦	Enter the business's (not the software licensee's) name;

♦	Indicate whether the business is a limited company or sole trader/partnership;

♦	Specify the year relating to the first month to be covered by the projections;

♦	Select the first month to be covered by the projections;

♦	Specify the currency symbol to be used throughout the model (preferably use a single character such as £, $ etc.);

♦	Indicate the primary currency denomination (units, 000s, Mln or Bn) to be used;

♦	Describe the main sales groups most appropriate to the company. These can relate to major product categories, market segments, customer groups, different VAT rate categories etc. Select definitions that are most relevant from the perspective of forecasting sales, material costs, credit terms etc. Detailed sales forecasts can be entered for user-definable subgroups within each main group.

2.	After pressing Done, this data will be automatically entered throughout the model and the appropriate months and years will be entered in all report headings, graph titles etc.

3.	Give the new model a title, for example First Attempt, using *Setup | Enter Model Title. This title will be inserted automatically in the footer of each printed report.


Using Exl-Plan - Using Quik-Plan - Procedure for Using Quik-Plan
Procedure for Using Quik-Plan

As the procedure for using Quik-Plan is virtually self-explanatory, the following guidelines are intended to serve only as a general introduction:

1.	To access Quik-Plan, select the QUIK tab or *Tools | Quik-Plan.

2.	Use the displayed button to clear any existing values in the Quik-Plan Assumptions Report.

3.	Enter assumptions. Refer to How Quik-Plan Handles Assumptions to see how Quik-Plan handles these assumptions and makes additional assumptions to complete the projections.

4.	Once assumption values have been entered into the Quik-Plan Assumptions Report, press Run Quik-Plan and wait a minute or two while Exl-Plan removes all existing assumptions from the Monthly and Quarterly Assumption Reports; inserts the Quik-Plan assumptions; adds its own assumptions; undertakes several recalculations; and updates the output reports. Note that Quik-Plan will abort this process if it detects any pre-existing assumption values within Monthly and Quarterly Assumption Reports that could not be cleared (see  Erasing Existing Values).

5.	Make a backup copy using the File | Save with Backup menu command.

This completes the Quik-Plan process and a full set of projections, including a balanced opening balance sheet, will have been compiled for the initial twelve months and subsequent two years (four years with Pro & Super versions). Graphs and reports showing all the detailed projections can be viewed or printed. For a quick synopsis of the projections, go to the Summary & Diagnosis Report (*Output | Summary) or refer to the Textual Summary Report (*Output | Textual).

Assumptions introduced by means of Quik-Plan can be changed or expanded within the Monthly & Quarterly Assumption Reports

For further details:

Guidance on Entering Assumptions

Avoiding Calculation Errors

Making Simple Changes


Model-Building Procedure

The following topics summarise and detail the procedures for using Quik-Plan:

Full Details:

Summary of Procedure for Using Exl-Plan to Generate Projections

Detailed Guidance on using Exl-Plan to Generate Projections


Summary of Procedure for Using Exl-Plan to Generate Projections

		Initiating Keystrokes
Load Excel with a copy of Exl-Plan and save it with a new file name		ALT+F, A
⇓
Remove all existing assumptions		ALT+S, L
⇓
Enter basic data about the business, projection start date, currency and main products		ALT+S, B
⇓
Give the new model a title		ALT+S, T
⇓
Enter monthly assumptions		ALT+A, S etc.
⇓
Enter balance sheet for start date & prior year P&L		ALT+U, N ALT+U, M
⇓
Enter quarterly assumptions for second & third years		ALT+A, Q
⇓
Enter assumptions for the fourth & fifth years (Pro & Super only)		ALT+ A, Y
⇓
Final recalculation		ALT+O, A
⇓
Save projections		ALT+F, S
⇓
View reports & graphs		ALT+U, ALT+C
⇓
Print reports & graphs		ALT+N, ALT+R


Detailed Guidance on using Exl-Plan to Generate Projections

To use Exl-Plan to make projections for a particular company, proceed as described in this four-step procedure:

♦	Set up Exl-Plan

♦	Enter Monthly Assumptions

♦	Enter Prior Year P&L & Opening Balance Sheet

♦	Complete the Projections

Note that calculation errors should be ignored until the procedure  3. Entering Prior Year P&L & Opening Balance Sheet has been completed.

Regularly use File | Save with Backup to make file copies when building a model. In addition to saving the current file, make copies using incrementing file names e.g. ABC-1.XLS, ABC-2.XLS and so on. This will help ensure that if a serious error arises within a model, it will always be possible to revert to an earlier error-free version.

Full Details:

1. Setting up Exl-Plan

2. Entering Monthly Assumptions

3. Entering Prior Year P&L & Opening Balance Sheet

4. Completing the Projections


1. Setting up Exl-Plan

1.	Load WORKPLAN.XLS and immediately re-save it using an appropriate new file name e.g. as XYZ-1.XLS.

2.	Decide how Exl-Plan's main sales groups should be utilised. These could refer to major product categories, market segments, customer types etc.

3.	Enter basic information about the business via the Basic Information dialog (*Setup | Enter Basic Model Info) as follows:

♦	Enter the business's (not the software licensee's) name;

♦	Indicate whether the business is a limited company or sole trader/partnership;

♦	Specify the year relating to the first month to be covered by the projections;

♦	Select the first month to be covered by the projections;

♦	Specify the currency symbol to be used throughout the model (preferably use a single character such as £, $ etc.);

♦	Indicate the primary currency denomination (units, 000s, Mln or Bn) to be used;

♦	Describe the main sales groups most appropriate to the company. These can relate to major product categories, market segments, customer groups, different VAT rate categories etc. Select definitions that are most relevant from the perspective of forecasting sales, material costs, credit terms etc. Detailed sales forecasts can be entered for user-definable subgroups within each main group.

4.	After pressing Done, this data will be automatically entered throughout the model and the appropriate months and years will be entered into all report headings, graph titles etc. If using multiple currencies, empty rows below assumption reports can be used to handle mixed currencies and their conversion into the model's base currency (refer to Making Simple Changes to Exl-Plan).

5.	Give the new model a title, for example First Attempt, using *Setup | Enter Model Title. This will be inserted automatically in the footer of each printed report.

6.	Use the *Setup | Clear All Assumption Reports menu command to remove all unprotected assumption values throughout the model. Exl-Plan will advise if it detects any assumptions that could not be automatically cleared (see Erasing Existing Values for further information).


2. Entering Monthly Assumptions

1.	Use the *Assumptions | Monthly - Sales menu command to go to Monthly Assumption Report No. 1 for Sales & Finished Stock Targets.

2.	Enter monthly assumptions in this report and then work through the remaining Monthly Assumption Reports. Concentrate initially on entering approximate or high-level values with a view to refining them once a full picture starts to emerge. For further assistance, refer to Guidance on Entering Assumptions and the Glossary of Terms. Make use of Exl-Plan's special facilities for entering constant and variable monthly values (*Assumptions | Constant Assumptions etc.) and their toolbar buttons. Save the file frequently and make regular back-up copies.

3.	Explanatory notes can be added to individual assumptions using Excel 5's Insert | Note facility or Insert | Comment in Excel 97 or higher.

4.	Use blank rows below Monthly Assumption Reports to build up detailed schedules, lists etc. and then use simple cell referencing to plug their total values into the appropriate assumption areas (see Plugging New Variables an Existing Variable for further information on this technique).

5.	Note that calculation errors may be indicated whenever the model recalculates using the *Tools | Calculate & Check menu command or the "C" button. These can be safely ignored until all the monthly assumptions and opening balance sheet data have been entered.


3. Entering Prior Year P&L & Opening Balance Sheet

1.	If desired, enter a summary of the prior year's P&L. Use the *Output | Monthly - Profit/Loss menu command to access the Monthly P&L, go to column F which is reserved for last/latest year's actual or anticipated P&L results and enter values in the unlocked (boxed-in) cells.

2.	Enter the outstanding data in unlocked (boxed-in) cells for the opening balance sheet (*Output | Monthly - Balances).

If using Exl-Plan Pro or Super, note that:

♦	Values for fixed assets, accumulated depreciation and opening government grants within the opening monthly balance sheet are picked up from entries in column F in Monthly Assumption Report No. 5.

♦	Values for total opening finished goods and material stocks are secured from column F in Monthly Assumption Reports Nos. 1 and 2 respectively.

Monthly Assumption Report No. 7 should be used to phase out the opening balances for certain items that appear in the opening balance sheet. If the totals being phased out differ from the opening balances, Exl-Plan will report an error whenever it recalculates using the *Tools | Calculate & Check menu command. If this happens, return to Report No. 7 and revise the assumption values in rows showing ERR instead of arrows in column B so that their total equates to the indicated opening value.

After entering all the necessary opening values, press F9 (or the crossed "C" button) and make sure the opening balance sheet is balanced by viewing the row for CHECK balance sheets balance immediately below the opening balance sheet. If this does not show a zero opening balance, review values entered for the opening balance sheet.

3.	Once the opening balance sheet balances, use the *Tools | Calculate & Check menu command to calculate and ensure that no calculation errors arise (see Avoiding Calculation Errors). Note that Exl-Plan's error trapping system only works when the *Tools | Calculate & Check menu command or the "C" button are used to recalculate.


4. Completing the Projections

1.	Re-save the file and make a further back-up copy.

Initial 12-month and first-year projections have now been completed and the appropriate reports and graphs can be viewed on the screen or printed.  Monthly assumptions can be fine-tuned and the resultant projections can be subjected to critical examination to determine their veracity etc. Be watchful for the following:

♦	Undesired blips in the cashflow projections arising from unsound assumptions about the phasing out of opening debtors or creditors (see Notes 1 and 3 in the Monthly Cashflow Projections).

♦	Data-entry errors like entering 300 instead of 3,000.

♦	Negative or excessively low/high production requirements arising from unrealistic stock level changes. (Pro & Super versions only.)

♦	Unplanned surges in finished goods stocks. (Pro & Super versions only.)

2.	Finish all the monthly projections before starting to enter quarterly assumptions. Use the two Quarterly Assumption Reports to prepare projections for the second and third years (*Assumptions | Quarterly). Refer to the calculated values for the first four quarters for guidance when entering assumptions for subsequent quarters. Use Exl-Plan's calculation & check menu command (*Tools | Calculate & Check) to recalculate and ensure that no calculation errors arise.

3.	For Exl-Plan Pro or Super, go to the 4th-5th Year Assumptions Report (*Assumptions | 4-5th Year Assumptions). Add annual assumptions for the fourth and fifth years using the calculated values for the second and third years as guides. Select *Tools | Calculate & Check to recalculate and ensure that no calculation errors arise.

4.	Save the updated model and make a back up with a new file name.

This completes the procedure for building a multi-year model. At this stage, assumptions can be further refined or Exl-Plan's sensitivity-analysis facility can be used to explore alternative scenarios (See Sensitivity Analysis). Exl-Plan Super's other planning tools can be utilised:

♦	Reviewing 'what-if' analyses (See What-If Analysis for First-Year Projections)

♦	Planning improved profitability (See Profitability Planner)

♦	Making short-term cash projections (See Short-Term Cashflow Projections & Chart)

♦	Updating first-year projections (See Trading Performance Tracker Report & Charts)

♦	Analysing long-term performance (See Long-Term Trend Analysis & Charts).

See also:

Avoiding Calculation Errors

Calculation Error Messages

Doing Sensitivity Analyses

Viewing 'What-If' Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Tracking Trading Performance

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Avoiding Calculation Errors

When using Exl-Plan, it is important to ensure that no calculation errors arise; that its sub-models produce identical results; and that no imbalances occur in the opening or projected balance sheets. When using Exl-Plan Pro or Super, a temporary imbalance will always occur in the opening balance sheet until all values have been entered in the Monthly Assumption Reports as well as in the opening balance sheet.

Because Exl-Plan does not use floating values to balance its balance sheets, it is very sensitive to formulae errors and consequently all structural changes to the model must be applied correctly. For example, if an additional monthly cashflow-related item is introduced into the model as an assumption row, changes may be needed in the monthly P&L, cashflow and balance sheet output reports as well as in their corresponding first-year output reports. In addition, corresponding changes may also be required in all the quarterly and annual output reports. For further guidance, see Making Complex Changes.

Exl-Plan displays on-screen messages when it detects calculation errors when the *Tools | Calculate & Check menu command or "C" button are used. These messages, detailed in Calculation Error Messages, cover the most likely error situations. In addition, Exl-Plan opens a help window describing the detected error and explaining how it can be resolved. This facility can be turned on and off using *Setup | Toggle Help for Calculation Errors.

Full Details:

Procedure to Help Locate & Eliminate Calculation Errors


Procedure to Help Locate & Eliminate Calculation Errors

1.	Scan the rows showing CHECK balance sheets balance immediately below the Monthly and Quarterly Balance Sheets for imbalances. Make sure that the opening balance sheet balances correctly. If it does not show a zero opening balance at the third decimal place, review all the values entered for the opening balance sheet.

2.	Check the right-hand column of the Check Balances Report in the CHECK worksheet (*Output | Check) for any differences between the key values computed by Exl-Plan's four sub-models.

3.	Examine Monthly Assumption Report No. 7 for errors and revise the assumption values in rows showing ERR instead of arrows in column B.

4.	If necessary, save the model with a new file name. Then, locate the source of the error by progressively removing all assumption values and recalculating using the *Tools | Calculate & Check menu command or "C" button.

5.	If the error cannot be located or easily fixed, the best option may be to revert to an error-free backup copy of the model.


Doing Sensitivity Analyses

Exl-Plan incorporates a powerful sensitivity-analysis facility which enables a user to temporarily override existing assumptions and to make global changes to the following key variables on a month-by-month basis in the first year and quarterly for the following years:

♦	Sales volumes

♦	Sales prices

♦	Materials/packaging costs

♦	Overheads

Of course, individual changes to assumptions can be made to specific monthly or quarterly assumptions by directly accessing and changing their values in the worksheet. In addition, Excel's Scenario Manager can be used to maintain multiple scenarios.

Full Details:

Procedure for using Sensitivity Analysis


Procedure for using Sensitivity Analysis

1.	Access the Sensitivity Analysis Factors Report in the SENS worksheet (*Tools | Sensitivity Analysis). Insert values for the appropriate months and/or quarters expressed as percentage increases/decreases for sales volumes, selling prices, material costs and/or overheads.

2.	Press Recalculate & Check. When undertaking sensitivity-analysis calculations, Exl-Plan makes selective adjustments throughout the Monthly and First-year sub-models as well as in the Quarterly and Annual sub-models. As a consequence, some values in the Monthly and Quarterly Assumption Reports may not sum correctly or be reconcilable with the original assumptions used. For example, total monthly sales may have changed in Monthly Assumption Report No. 1 but the underlying sales volumes and prices remain unaltered.

3.	To revert to the initial position, remove all the values in the Sensitivity Analysis Factors Report by pressing Zeroise Factors.

Exl-Plan will indicate if sensitivity analysis is in operation whenever the *Tools  | Calculate & Check menu command or "C" button are used.

Generally, the sensitivity-analysis factors modify assumptions in exactly the same way as could be achieved by altering every single one of the underlying relevant assumptions directly. For example, if monthly sales volumes are increased by a specified percentage using its sensitivity factor, sales turnover would be increased by the same percentage as would be stock levels, materials/goods purchases, direct costs (including direct labour), VAT receipts/payments, creditor/debtor levels etc. In contrast, an increase in selling prices would only increase sales turnover, VAT receipts and debtors while leaving costs, stocks and creditors all unchanged.

See also:

Viewing 'What-If' Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Tracking Trading Performance

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Viewing What-If Analyses

 Once first-year projections have been completed, Exl-Plan automatically generates 'what-if' tables which show the impact on projected first-year profitability of incremental changes in (i) sales volumes and/or selling prices and (ii) cost of sales and/or overhead costs.

Access the What-If Analysis via the WHAT-IF worksheet (*Tools | What-If Analysis). Observe that it incorporates some dynamically-linked interpretations of what-if analyses.

Any changes to underlying monthly assumptions are immediately reflected in the 'what-if' tables as soon as Exl-Plan recalculates (*Tools | Calculate All & Check).

See also:

Doing Sensitivity Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Tracking Trading Performance

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Planning Better Profitability

The Profitability Planner in the PROFIT worksheet (*Tools | Profit Planner) comprises a DuPont-type profitability chart derived from the first-year projections. It focuses on Return on Total Assets (net profit before tax as % total assets) as the prime measure of profit and shows graphically how this index is derived from first-year assumptions and forecasts. This Planner also allows a user to explore the impact of specific changes in key variables and assumptions on overall first-year profitability.

Full Details:

Procedure for Planning Better Profitability


Procedure for Planning Better Profitability

1.	Compile preliminary first-year projections using either Quik-Plan or, preferably, by entering detailed assumptions into the Monthly Assumption Reports.

2.	Press the Update Values button on the PROFIT worksheet to copy projected values (located in the upper cells of boxes)  for each variable down one row into the yellow-coloured unprotected cells. These values are derived by formulae from the first-year and monthly projections.

3.	Change values within the yellow-coloured, unprotected cells individually (or in combinations to take account of interactions). Examples:

♦	An increase in selling prices might require a corresponding increase in debtors as well as possible reductions in sales volumes leading to lower cost of sales and reduced creditors. Values in the yellow-coloured cells must be changed manually and one-by-one to reflect management's assessments of the possible consequences of increased in prices.

♦	A substantial increase in sales volumes could entail a higher cost of sales and might also need additional fixed assets as well as higher debtor, creditor & stock levels and possibly increased overheads.

4.	Press the Recalculate Ratios button on the PROFIT worksheet to update the profitability projections shown in the uncoloured, lower cells of boxes.

Once a creditable and more profitable scenario has been formulated, the underlying monthly assumptions can be revised in detail to produce updated, integrated projections.

See also:

Doing Sensitivity Analyses

Viewing 'What-If' Analyses

Making Short-Term Cash Projections

Tracking Trading Performance

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Making Short-Term Cash Projections

Once first-year projections have been compiled, monthly cashflow projections for the first three months of the first-year can be expanded into approximate weekly projections using the CASH worksheet (*Tools | Short-Term Cashflow).

This process entails estimation of the percentage weekly distribution of the monthly cashflows. For example, in a non-cash business, creditor payments often peak during the first week or two of a month while debtor receipts may be concentrated towards the month end. This pattern can be reflected by setting the percentage distributions for each cashflow item (row) within the unprotected (cream) areas of the CASH worksheet.

To clear existing percentage values, locate the cursor within the CASH worksheet and use *Setup | Clear Current Assumption Report.

To update values in the CASH worksheet, use *Tools | Calculate Sheet, or *Tools | Calculate All & Check, or the corresponding toolbar buttons. Note that the total percentage distribution for each item (row) must equate to either 100% or 0% otherwise an error will be indicated on the worksheet when it recalculates.

See also:

Doing Sensitivity Analyses

Viewing 'What-If' Analyses

Planning Better Profitability

Tracking Trading Performance

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Tracking Trading Performance

This facility is located at the TRACK worksheet (*Tools | Performance Tracker). It allows a user to produce updated first-year projections of sales and profitability by combining year-to-date actuals with projections for the remaining months of the year. It focuses on strategic items and is not intended to serve as a detailed performance analyser (see Extending Exl-Plan for procedures to expand Exl-Plan). Once projections have been compiled, TRACK can be used to help identify significant variations in performance and to prompt the updating of plans and projections so that necessary actions are initiated in good time.

Pre-formatted charts highlight any deviations in sales forecasts for each main sales group and present updated projections combining year-to-date actuals with projections for remaining months of the year.

This facility provides feedback on the accuracy of key projections and can be used to trigger a review of projections and their underlying assumptions. This can be done within Exl-Plan as indicated in the following example:

Assume that, after trading for the first five months of the current financial year, cumulative sales are running 10% below projected. By using TRACK to combine year-to-date actual sales with the original projections for the remaining seven months of the year, a quick forecast of the likely full-year outcome can be secured.

However, this forecast could be erroneous as there is no guarantee that sales will pick up suddenly (by 10%) and match the projections originally made for the final seven months of the year. In these circumstances a revision of the monthly projections could be called for. In the first instance, this could be done quickly using Exl-Plan's monthly sensitivity analysis to depress sales volumes or prices for the outstanding months and generate updated full-year forecasts.

Depending on the degree of divergence between actuals and projected, a complete review of assumptions and projections might be justified and this could result in a completely new set of projections of trading performance and cashflow. In the example above, these should be started at the sixth month of the financial year rather than at the first month.

Full Details:

Procedure for Tracking Trading Performance


Procedure for Tracking Trading Performance

1.	Compile and finalise first-year projections by entering assumptions throughout the Monthly Assumption Reports.

2.	To clear any existing historic values, locate the cursor within the TRACK worksheet and use *Setup | Clear Current Assumption Report.

3.	Enter actuals for each completed month within unprotected (cream) areas of the TRACK worksheet for the main sales groups, gross profit, overhead costs and net profit before tax.

4.	Update the TRACK worksheet using *Tools | Calculate Sheet, or *Tools | Calculate All & Check, or the corresponding toolbar buttons.

See also:

Doing Sensitivity Analyses

Viewing 'What-If' Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Analysing Long-Term Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Analysing Long-Term Performance

This facility is located at the TRENDS worksheet (*Tools | Long-Term Trends). It allows an established business to compile tables which combine actuals for the past five years with projections for the next five and to generates ratios and performance measures for up to ten years.

Once the TRENDS worksheet has been updated, the resultant ratios and charts can be reviewed to identify any significant fluctuations in performance and to assess the realism of projections by reference to historic performance.

Full Details:

Procedure for Analysing Long-Term Performance


Procedure for Analysing Long-Term Performance

1.	Compile preliminary five-year projections using either Quik-Plan or, preferably, by entering assumptions in the Monthly, Quarterly & Annual Assumption Reports and by inserting the prior-year P&L in the M_PL worksheet and opening balance sheet within M_BS.

2.	To clear any existing actual values, locate the cursor within the TRENDS worksheet and use *Setup | Clear Current Assumption Report.

3.	Enter historic P&L and balance sheet values for one or more years. Note that the cells within the range B3 to E4 are unprotected to allow historic year numbers and month numbers to be manually inserted.

4.	Update the TRENDS worksheet using *Tools | Calculate Sheet, or *Tools | Calculate All & Check, or the corresponding toolbar buttons.

See also:

Doing Sensitivity Analyses

Viewing 'What-If' Analyses

Planning Better Profitability

Making Short-Term Cash Projections

Tracking Trading Performance

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Changing Exl-Plan - Changing Exl-Plan
Changing Exl-Plan

Whilst many users will use Exl-Plan exactly as supplied, others may wish to take advantage of their Excel expertise to tailor or extend it to meet specific requirements.

Some general advice:

♦	Never delete or alter the supplied range or worksheet names or interfere with the macros supplied with Exl-Plan.

♦	When starting to change an Exl-Plan model, always work off a copy of an error-free version.

♦	Whenever a model has been changed or extended, always fully test it to ensure that the changes work as intended and have no undesirable knock-on consequences.

♦	When making large-scale changes, use *Protection | Unprotect All Worksheets to disable protection throughout the model, or use *Protection | Unprotect This Worksheet to remove protection from the current report.

♦	*Protection | Unlock Range can be used to unlock a selected range.

♦	Having removed protection to make changes within a workbook, always restore protection as soon as practicable.

Do not rename an Exl-Plan file using FileMgr or Explorer as  this causes the file to 'lose' references to its toolbar. Instead, load the file using Excel and then save it with a new name.

It may be worthwhile saving a modified version of Exl-Plan as a template file (*.XLT) in the Exl-Plan subdirectory (with Windows 3.1). If using Excel 7 or higher with Windows 95/98/NT, save the template in Microsoft Office's Templates\ Spreadsheet Solutions folder. Refer to Excel's Online Help for guidance on creating and using templates

Users should never alter Exl-Plan's toolbar, buttons, dialog boxes, macro modules, range names or worksheet names. If these are altered in any way, either by accident or design, Invest-Tech cannot accept any responsibility for support or give support in the restoration of a modified file. It is the responsibility of the user to ensure that a fully intact copy of Exl-Plan is always available as a backup or fall back.

Further details:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Making Simple Changes

The topics below explain how simple but highly effective changes can be made to Exl-Plan by users with only minimal expertise with Excel:

♦	Changing the name of an existing variable

♦	Entering new assumption rows

♦	Plugging new variables into an existing variable

♦	Extending the Power of Plugging In

♦	Using formulae instead of values

♦	Changing report layouts

♦	Combining simple changes.

Note that the *Setup | Clear All Assumption Reports or Clear Current Assumption Report menu commands only remove assumption values in unlocked cells within assumption reports. To use this facility after adding new assumption rows or new blocks of assumption values, the newly added cells must be unlocked (using the *Protection | Unlock Range option or the unlock button) to enable their assumption values to be cleared by the foregoing commands.

Refer also to the following types of complex changes:

General Procedure for Complex Changes

Inserting a New Main Sales Group

Inserting an Additional Monthly Expense Item

Inserting a New Balance Sheet Item

Deleting a Row

Changing Intervals & Time Horizons

Changing Column Widths

Refer also to the following expansion possibilities for Exl-Plan:

Incorporating Actuals with Projected

Updating Projections with Actuals

Consolidating Projections


Changing the Name of an Existing Variable

To change the name (but not the underlying context) of a locked variable, e.g. to replace the description of an expense item in the Monthly Assumption Report for Overheads, follow the procedure below.

Full Details:

Procedure for changing Name of an Existing Variable


Procedure for changing Name of an Existing Variable

1.	Get into the M_O worksheet.

2.	Go to the cell in question; unlock it (*Protection | Unlock Range or unlock button).

3.	Make the desired change.

4.	Lock the cell again (*Protection | Lock Range or lock button).

 Repeat this procedure if the original description appears in any of the output reports and is not updated automatically using built-in formulae.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Entering New Assumption Rows

It is a simple matter to insert new rows for assumptions within the existing boxed-in areas for accommodating assumptions. To ensure that the values in new rows are always embraced by SUM formulae etc., for the boxed-in area, do NOT insert new rows as replacement top or bottom rows.

fr =SUM formulae embracing rows

fc = SUM formulae embracing columns

In the example above, new rows should be inserted between Product A and Product D. Note that additional SUM formulae (fc) for the twelve-month totals must be inserted for each new row.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Plugging New Variables into an Existing Variable

This is one of the easiest and most powerful changes that can be make to an Exl-Plan model. It involves using empty rows below an Assumption Report and then plugging their values into an existing row within the Report by entering simple addition or summing formulae in lieu of assumption values.

For example, it is desired to expand assumptions relating to monthly sales volumes for a particular sales group to accommodate monthly forecasts for, say, twenty individual products. To do this, follow the procedure below.

Full Details:

Procedure for Plugging New Variables into an Existing Variable


Procedure for Plugging New Variables into an Existing Variable

1.	Go to Monthly Assumption Report No. 1 (M_S) and turn off protection for its worksheet (*Protection | Unprotect This Worksheet).

2.	In empty rows below the Report, add appropriate product description labels for each of the twenty products in column A and then enter monthly sales forecasts in columns G to R. Unlock these new assumption cells.

3.	Within the Assumptions Report, insert SUM formulae in one of the original assumption rows for the sales group to total all the monthly values entered into the new rows.

4.	Restore the worksheet's protection (*Protection | Protect This Worksheet).

No other changes are required aside from inserting weighted average selling prices for the expanded main sales group for each month in the appropriate row for Net selling prices. If different prices are available for each product, proceed as follows:

1.	Add a further twenty rows to accommodate the various monthly prices.

2.	Use twenty more rows to calculate total monthly sales for each product.

3.	Calculate weighted average prices for each month.

4.	Use formulae to plug these into the row for Net selling prices, which relates to the extended sales group.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Extending the Power of Plugging In

Logical extensions of the use of simple formulae to plug into existing variables within an Exl-Plan model include the following:

♦	Inserting new worksheets to incorporate very detailed sales projections (for individual products, customers etc.). These could be summarised and plugged into the existing main sales groups using SUM formulae.

♦	Using Copy & Paste Special | Link to pull aggregated sales data from another Excel file into the Exl-Plan model.

♦	Simply copying & pasting values into existing assumption blocks or into specifically designated blocks below assumption reports within Exl-Plan.

The potential to expand Exl-Plan in this way is virtually unlimited. For example, it could be used to incorporate parts explosions, lists of materials, detailed cost analyses, manning levels, capital expenditure schedules and much more.

Very detailed or specialised reports could be created and their total values plugged into the rows and variables supplied with Exl-Plan.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Using Formulae Instead of Values

By inserting formulae instead of values in assumption rows, it is possible to reduce the number of values to be entered every time the model is altered.  For example:

♦	Formula could be used to determine production manpower by relating planned production to projected labour output rates, working hours per month etc. As a result, every change in sales or stocks would be automatically reflected in production manpower levels.

♦	A formula could be used to repeat a constant assumption value entered for the first month in the cells for each subsequent month.

The scope for using formulae is considerable. For example, they could be used to incorporate foreign currency exchange rates, seasonal factors and to determine output or sales on the basis of workdays per month.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Changing Report Layouts

Where special report layouts must be used for presentation purposes or to conform with in-house styles, the simplest solution is to insert additional worksheets containing these new reports laid out exactly as desired and incorporating simple cell reference formulae (e.g. +A10) to link back into cells within existing Exl-Plan reports. This approach completely avoids the need to make any structural changes to Exl-Plan's existing output reports.

The summary monthly output report (M_SY) and all the first-year and annual reports are examples of this type of easily constructed report.

Using this approach, it is very simple to construct reports in foreign languages which utilise the same data as the underlying model.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Combining Simple Changes

A series of simple changes can be readily combined to make very substantial alterations to the supplied model and to turn it into an extremely comprehensive planning tool which combines very detailed projections for the first year with broader strategic forecasts for subsequent years.

A modified version of Exl-Plan can be saved as a template file (*.XLT) in the Exl-Plan subdirectory. If using Excel 97 or higher, save the template in the Program Files\Microsoft Office\Templates\Spreadsheet Solutions folder.

Note that, provided its integrity has not been compromised as result of modifying any supplied formulae, Exl-Plan's facility for global sensitivity-analysis can be used to temporarily over-ride existing forecasts for sales volumes, sales prices, material costs and overheads without having to manually change all the underlying detailed assumptions.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Making Complex Changes

The methods described in Making Simple Changes should be used wherever possible in preference to making any of the following more complex changes:

♦	General procedure for complex changes

♦	Inserting a new main sales group

♦	Inserting an additional monthly expense item

♦	Inserting a new balance sheet item

♦	Deleting a row

♦	Changing intervals and time horizons

♦	Changing column widths

To alter the basic structure, logic etc. of Exl-Plan, proceed on a step-by-step basis with great care to avoid possible knock-on errors etc.  For example, if a completely new type of variable needs to be added to a Monthly Assumption Report, it may also be necessary to change several Monthly Output Reports as well as all the First-year, Quarterly and Annual Output Reports.

As it can take much more time to test changes than to make them in the first instance, complex changes should only be introduced when adequate time is available to fully test the altered model and to check that existing formulae have not been disrupted. Note that while Exl-Plan's error-trapping facilities will help locate mathematical errors, they will never detect mistakes created by faulty logic etc.

Only users who are very familiar with both Exl-Plan and Excel should undertake the structural changes described below. Substantial changes should be made only when strictly necessary and where workarounds based on the simpler solutions described in Making Simple Changes are likely to be much easier and quicker to implement. Where substantial changes are required, consideration should be given to engaging Invest-Tech to undertake the required modifications (Refer to Model Development Service).

See also the following simple changes:

Changing the Name of an Existing Variable

Entering New Assumption Rows

Plugging a New Variable into an Existing Variable

Extending the Power of Plugging In

Using Formulae Instead of Values

Changing Report Layouts

Combining Simple Changes

Refer to the following expansion possibilities for Exl-Plan:

Incorporating Actuals with Projections

Updating Projections with Actuals

Consolidating Projections


General Procedure for Complex Changes

When making any substantial changes to Exl-Plan, always adhere to the general procedure indicated below.

Use the Check Balances Report (CHECK) to help identify any errors resulting from changes.  Also, refer to the CHECK balance sheets balance rows immediately below the Monthly and Quarterly Balance Sheet Reports to locate possible errors. Compare the output of the altered model with that of the original to help locate errors and to check calculations and results.

If making changes to formulae in the Quarterly Assumptions Report, bear in mind that completely different formulae are used for each of the first four quarters. Accordingly, all changes to these formulae must be done on a cell-by-cell basis.

Full Details:

General Procedure for making Complex Changes


General Procedure for making Complex Changes

1.	Confirm that the initial model has no calculation errors.

2.	Save and back up the original model before it is modified and give a new file name to the model that is about to be changed.

3.	Think through the planned changes from a financial perspective by assessing their impacts on P&Ls, cashflows, balance sheets for months, quarters and years ahead and their possible consequences for the model.

4.	Complete major changes one at a time.

5.	Test each change by using dummy values that can be readily recognised, for example, 1000.

6.	Always recalculate using Exl-Plan's *Tools | Calculate & Check menu command or the "C" button in order to avail of its error-trapping facilities.

7.	Eliminate errors as soon as detected and confirm that no calculation or formulae errors exist before starting the next change.

8.	Check that the changes give the desired or expected results by experimenting with very large positive (and negative, where appropriate) values.

9.	Save the modified version of Exl-Plan as a template file (*.XLT) in the Exl-Plan subdirectory. If using Excel 97 or higher, save the template in the Program Files\Microsoft Office\Templates\Spreadsheet Solutions folder.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Inserting a New Main Sales Group

Where the supplied main sales groups and their sub-groups are insufficient, an extra main sales group can be added to various assumption and output reports.

Full Details:

Procedure for Inserting a New Main Sales Group


Procedure for Inserting a New Main Sales Group

1.	Insert new rows in the midst of every block of rows used for existing main sales groups within each Monthly and Quarterly Report. This will ensure that values for the new group are automatically embraced by SUM formulae used to total values for the sales groups. When inserting new rows, always sure that they go into the same relative position within each block e.g. always the second one down or second last from the bottom.

2.	Specify an appropriate description for the new group and enter assumption values for sales, prices, stocks, labour, materials, VAT etc. into all newly-inserted assumption rows.

3.	For newly-inserted rows which require formulae instead of assumption values, use Copy & Paste to copy formulae from an adjacent row either downwards or upwards into the new rows.

4.	Enter test assumption values, which are easily recognised, check for errors and scrutinise the new rows and all relevant totals for possible errors or anomalies.

5.	Check that totals in the First-Year and Annual Reports are correct.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Inserting an Additional Monthly Expense Item

To insert an additional monthly expense item (where it is not practical to rename an existing expense item or to plug into an existing item), a new row must be inserted for the item in the appropriate Monthly Assumption Report as well as in the Monthly Cashflow Projections.

If, for example, it is essential to insert a new variable within a specific monthly overhead category (such as Operational Overheads, Management/Admin Staff Overheads etc.), proceed as indicated below.

Full Details:

Procedure for Inserting an Additional Monthly Expense Item


Procedure for Inserting an Additional Monthly Expense Item

1.	Go to the Assumption Report for Monthly Overheads and remove its protection. Insert a new row immediately below an existing variable within the relevant overhead category.  Enter the new variable's name and monthly values. (If using Exl-Plan Pro or Super, indicate the expected credit period in column B).

2.	Edit the first month's formula for total costs for the overhead category to embrace the new row and then copy it across the twelve months. Enter a SUM formula for the full-year total for the new row.

3.	Insert a new row in the Monthly Cashflow Projections Report immediately below the row for the pre-existing variable referred to in Point 1 above. Copy all the formulae in this variables row downward into the newly created row.

4.	The new expense item may also need to be included in the VAT calculation formulae for inputs in Assumption Report No. 6 in the row entitled Total VAT on non-pay expenses.

5.	If the new row relates to an additional employment category, it will be necessary to ensure that related payroll costs are included in the monthly P&Ls and that the formulae for calculating wages & salaries and PAYE in the Monthly Cashflow Projections are adjusted. Formulae in the Monthly Performance Review Report used to calculate average number of employees would also need to be revised.

6.	Finally, it may be necessary to make provision for the new row to be adjusted by the appropriate sensitivity-analysis factor for sales volumes, sales prices, material costs or overheads.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Inserting a New Balance Sheet Item

To add a new balance sheet item, additional rows must be inserted in the appropriate Monthly Assumption Reports to accommodate the related assumptions. Additional rows containing formulae will be required in the Monthly and First-year Cashflow Projections and Balance Sheets as well as in some Quarterly and Annual Reports. In addition, it may be necessary to alter formulae in the Monthly, First-Year, Quarterly and Annual Performance Reviews. The new item may also need to be included in the VAT calculation formulae in Monthly Assumption Report No. 6.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Deleting a Row

Delete a row only where strictly necessary as this may require adjustments to numerous formulae in several reports. Instead, consider hiding the row or suppress its printing (using *Setup | No Empty Rows Printed).

In most circumstances, it is likely to be much simpler and quicker to create specially tailored reports as described in Changing Report Layouts than to modify Exl-Plan's existing output reports by deleting rows.

The procedure for deleting rows is indicated below.

Full Details:

Procedure for Deleting a Row


Procedure for Deleting a Row

1.	Save the unaltered model.

2.	Delete the first row, calculate using the F9 function key and then proceed to search column-wise for #REF signs.

3.	Locate the first formula initiating an error, remove references to the deleted row in this formula and recalculate.

4.	Continue this search process until the model is error-free as confirmed by the Check Balances Report (CHECK).

5.	Delete the next row and repeat the foregoing procedure for locating and removing any errors.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Changing Intervals & Time Horizons

It is possible to modify Exl-Plan to extend its monthly projections into a second year by carefully inserting additional monthly columns and moving and copying formulae in such a way that their contents and range names remain intact. However, if monthly projections are required for a second year, the simplest solution is to daisy-chain two copies of Exl-Plan using Copy & Paste Special | Link so that the closing balance sheet for the first year is used as the opening balance sheet for the second year within the second copy.

To create a 13-period year, instead of 12-months, an additional column must be inserted into each monthly assumption and output report. Formulae for calculating wages, depreciation, interest etc. on the basis of 4.33 weeks per month or 12 month per year will need adjustment. Integration and allocation of a 13-period first year between the four quarters for the first year within the Quarterly Assumptions Report will also need to be addressed.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Changing Column Widths

As an alternative to changing column widths, consider changing cell formats (by reducing decimal places or removing the punctuation used to indicate 000s) or use a higher currency denomination (e.g. Mln instead of 000s).

When changing column widths, apply the new widths consistently and bear in mind the need to accommodate the printing of reports. Do not change the widths of columns A to F.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Extending Exl-Plan

Exl-Plan can be used without modification to create financial projections for a business; assist with the preparation of budgets; or contribute to business appraisal or planning exercises. It provides all the reports normally used for these purposes.

Exl-Plan can be extended with little effort to perform other functions to assist the planning and management of a business. The following procedures are described:

♦	Incorporating actuals with projections

♦	Updating projections with actuals

♦	Consolidating projections.

Before contemplating expansion of Exl-Plan, check out the topic Tracking Trading Performance for a simple built-in performance monitoring facility.

See also:

Making Simple Changes

Making Complex Changes


Incorporating Actuals with Projections

Exl-Plan can be extended to generate a detailed analysis of projections and actuals and to produce monthly and year-to-date totals and variance reports. Before contemplating expansion of Exl-Plan, check out the topic Tracking Trading Performance for a simple built-in performance monitoring facility.

The extension described below should use additional worksheets to accommodate separate reports for each month. Each monthly report should contain rows of variable descriptions derived from those used in Exl-Plan's assumption and output reports. The following columns should be created for each monthly report:

♦	Projected monthly values obtained by means of simple cell references to sales and expense values within Exl-Plan's assumption and output reports.

♦	Actual monthly values to be entered manually or imported

♦	Monthly variances (absolute amounts and percentages) based on the projected and actual monthly values

♦	Cumulative projected values derived by summing projected monthly values for the year-to-date

♦	Cumulative actual values derived by summing actual monthly values for the year-to-date

♦	Cumulative monthly variances (absolute amounts and percentages) based on cumulative projected and actual year-to-date values.

To create this system, only a basic understanding of Exl-Plan and a moderate level of spreadsheet expertise are required.

If Exl-Plan is being used to compare actuals with projections, its global sensitivity-analysis facility can still be used to flex assumptions and projections for the remaining months of the year.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Updating Projections with Actuals

Whilst Exl-Plan was never intended to serve as a management accounting system, its projections can be updated by actuals using one of the two methods described below. Before contemplating expansion of Exl-Plan, check out the topic Tracking Trading Performance for a simple built-in performance monitoring facility.

The first and simplest method is to create a duplicate Monthly P&L Report which combines actuals year-to-date with projections for the remaining months of the year.  This straightforward task is explained below.

Full Details:

Procedure for Updating Projections with Actuals


Procedure for Updating Projections with Actuals

1.	Save a copy of the unmodified model as a back up in case any unforeseen problems arise.

2.	Use the space below the Monthly Profit & Loss Accounts to create a duplicate copy of the Accounts. Use a simple cell referencing formula to link the top left-hand cell in the duplicate P&L to the corresponding cell in the original P&L. Next, copy and paste this formula across columns (A to U) and downwards in order to create the duplicate P&L.

3.	Next, enter SUM formulae in the full-year column (S) of the duplicate P&L to total the monthly values. Insert new formulae for the As % Sales and % Change in Year columns.

4.	Lock all the cells in the duplicate P&L.

5.	The duplicate P&L report should now be complete and any changes made in the original P&L will be automatically reflected in the duplicate.

6.	As the year progresses, unlock and over-write the cell reference formulae in the duplicate report by entering actuals abstracted from management accounts. Whenever the model recalculates, the full-year totals in the duplicate P&L report will contain actuals for the year-to-date and projections for the remaining months of the year.

7.	Because none of Exl-Plan's original formulae have been disrupted, monthly assumptions impacting on projected profit & loss accounts for the remaining months of the year can be updated at any stage to take account of recent performance trends and to give a more reliable indication of the likely out-turn for the full year.

A second method of updating projections with actuals involves overwriting assumptions within the Monthly Assumption Reports by actuals that have been abstracted from monthly accounts.  Projections for the remaining months can then be updated in the light of experience or changing circumstances. With some experience of overwriting and updating projections, it should be possible to make relatively accurate adjustments to the original assumptions for sales, costs etc. in order to reflect actual achievements. However, it is likely to be more difficult to tune the cashflow projections and balance sheets to reflect results because of interactions and complexity of the formulae and calculations involved.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Consolidating Projections

Several different Exl-Plan models can be used to produce consolidated projections for a group of businesses. These can be used in conjunction with Excel's Data | Consolidate menu option.

The procedure is described below. This ensures that changes to assumptions and resultant projections for subsidiary businesses will be automatically reflected in the consolidated projections.

Full Details:

Procedure for Consolidating Projections


Procedure for Consolidating Projections

1.	Use Exl-Plan to build separate models for each business to be consolidated. Add a new worksheet to each model and create identical summary consolidation reports incorporating all the elements that need to be consolidated. Typically, each consolidation report would comprise monthly or annual P&Ls, cashflows and balance sheets. Use simple cell referencing to extract the appropriate values from within Exl-Plan's worksheets.

2.	In a newly created consolidation file, construct a copy of the summary consolidation report on the first worksheet and insert as many worksheets as there are businesses to be consolidated.

3.	Use Copy & Paste Special Link to replicate the summary consolidation reports for each business in the stacked worksheets behind the first sheet within the consolidation file. Let the first business's report become the second sheet, the second business's report become the third sheet and so on.

4.	Within the consolidation file, make adjustments to take account of inter-company trading, foreign currency conversion etc.

5.	Enter simple SUM formulae in the first worksheet of the consolidation file to aggregate values in the stacked worksheets.

See also:

Making Simple Changes

Making Complex Changes

Extending Exl-Plan


Support Policy for Exl-Plan

Exl-Plan is supplied under licence to users strictly on an as is basis. Once a user gets Exl-Plan to load satisfactorily, Invest-Tech considers its role and commitment as supplier to have concluded. This limitation is due to the very open nature of Exl-Plan and the difficulties involved in assisting users who may have altered Exl-Plan, either intentionally or by accident. However, Invest-Tech will provide support to registered bona fide users for three months (and longer at Invest-Tech's discretion) to ensure that, in the unlikely event of problems arising, these are kept to the minimum and are quickly resolved.  This support is primarily intended to cover the following:

♦	Initial macro failures.

♦	Calculation errors where the layout, logic, formulae, worksheet & range names and macros in Exl-Plan have NOT been altered by the user.

♦	Guidance to users encountering unexpected results when entering assumptions in an unaltered model.

Before contacting Invest-Tech about a possible problem or error, a user should carefully check the Help System for guidance.  When seeking help from Invest-Tech, provide precise details of the events leading up to the difficulty; indicate the hardware system being used; and describe any changes made to Exl-Plan. In some circumstances it may be necessary to forward a copy of the model, in strictest confidence, to Invest-Tech for detailed examination.

For general support queries about Exl-Plan, refer to the page of Frequently Asked Questions within Invest-Tech's PlanWare web site at www.planware.org/exlfaq.htm.

Invest-Tech cannot support Exl-Plan users who acquire future releases of Excel on the grounds that Exl-Plan cannot be future-proofed to anticipate any incompatibilities or changes introduced in future releases of Excel. However, Invest-Tech intends to upgrade and enhance Exl-Plan in step with new releases of Excel. These upgrades will be offered to existing users at discounted prices. If users of Exl-Plan upgrade their copies of Excel, they should retain their earlier release of Excel until (a) they are completely satisfied that Exl-Plan runs satisfactorily with the new release of Excel or (b) they have upgraded Exl-Plan to match the new release of Excel.

For help on hardware and printer problems, users should contact their dealer in the first instance and specific Excel problems should be addressed to Microsoft Corporation.

See also:

Model Development Services

Other Invest-Tech Products

Contacting Invest-Tech

About Invest-Tech


Model Development Services

Invest-Tech will give limited general guidance at no charge to users wishing to make structural changes to Exl-Plan. It also offers a model-building or tailoring service on a fee-paying basis to help users to expand or modify Exl-Plan to meet particular requirements. This work is undertaken on a confidential basis and to the highest professional standards in accordance with the Code of Professional Conduct of the Institute of Management Consultants in Ireland. The fee arrangements are as follows:

♦	A written proposal describing the proposed work, fees, etc. will be presented to the prospective client and written acceptance will be requested before any work commences.

♦	Fees will be charged at Invest-Tech's standard rates on the basis of the actual time input.  Telephone/fax/courier expenses will be recharged at cost.  VAT (currently 21%) will be added in the case of Irish-based clients and unregistered clients in other EU countries.

Invest-Tech can also provide one-to-one or in-company training on Exl-Plan which can be linked to its development and use for a specific modelling application.

See also:

Support Policy

Other Invest-Tech Products

Contacting Invest-Tech

About Invest-Tech


Other Products

Exl-Plan is one product in a very wide range of stand-alone and spreadsheet-based financial planning software packages developed by Invest-Tech. As similar basic structures, menus etc. are used throughout the range, conversion from one product to another is very straightforward.

Latest information on products, access to downloadable shareware versions and general advice on many aspects of business planning and development are available at Invest-Tech's WWW site located at the following URL:

www.planware.org

Invest-Tech welcomes feedback from users and invites suggestions for improving and extending Exl-Plan.

See also:

Model Development Services

Contacting Invest-Tech

About Invest-Tech


Contacting Invest-Tech

Invest-Tech can be contacted as follows:

Tel: 283 4083 (24-hr answering)

Fax: 278 2391

Dial codes: IRL: 01-    UK: 00-353-1-    US: 011-353-   Other:  +353-1-

Email:  info@planware.org

Address:   Invest-Tech Limited, 27 Ardmeen Park. Blackrock, Co Dublin, Ireland

www.planware.org

See also:

Support Policy

Model Development Services

Other Invest-Tech Products

About Invest-Tech


List of Reports

Monthly Assumptions:
1.  Sales & Finished Stock Targets
2.  Cost of Materials/Goods, Stocks & Purchases
3.  Direct Manpower, Other Direct Costs & Cost of Sales
4.  Overheads
5.  Fixed Assets, Capex, Leasing & Capital Grants
6.  Funding, Interest Rates, VAT & Related Items
7.  Opening Balance Sheet Items, Creditors & Debtors

Monthly Projections:
Profit & Loss Accounts (with prior year actual/estimated)
Cashflow Projections (with notes)
Balance Sheets (with opening balance sheet)
Performance Review
Summary

First-year Projections:
Profit & Loss Accounts (with prior year actual/estimated)
Cashflow Projections (with notes)
Balance Sheets (with opening balance sheet)
Performance Review
Summary

Quarterly Assumptions & Projections:
Assumptions for 2nd & 3rd Years and Projections for 1st Year - Sales & Costs
Assumptions for 2nd & 3rd Years and Projections for 1st Year - Other Items
Profit & Loss Accounts (with prior year actual/estimated)
Cashflow Statements (with reconciliation to P&Ls)
Balance Sheets (with opening balance sheet)
Performance Review

Five-year Projections:
Assumptions for 4th & 5th Years
Profit & Loss Accounts (with prior year actual/estimated)
Cashflow Statements (with reconciliation to P&Ls)
Balance Sheets (with opening balance sheet)
Performance Review

Other Reports:
Check Balances Report
Quik-Plan Assumptions Report
Textual Summary Report
Sensitivity Analysis Report
Summary & Diagnosis Report - 3 Years
What-If Analysis for First-Year Projections
Profitability Planner
Short-Term Cashflow Projections
Trading Performance Tracker Report
Long-Term Trend Analysis

See also:

List of Graphs

Guidance on Entering Assumptions


List of Graphs

Months:
Fig 1. Sales Analysis
Fig 2. Sales & Profits
Fig 3. Cost Analysis
Fig 4. Key Indicators
Fig 5. Cashflow
Fig 6. Manpower
Fig 7. Material Costs
Fig 8. Gross Profits

Quarters:
Fig 9. Sales & Profits
Fig 10. Cashflow
Fig 11. Key Indicators
Fig 12. Financial Ratios
Fig 13. Working Capital
Fig.14. Capital Expenditure

Five Years:
Fig 15. Sales & Profits
Fig 16. Cashflow
Fig 17. Key Indicators
Fig 18. Financial Ratios
Fig 19. Working Capital

Other:
Short-Term Cashflow Projections Chart
Trading Performance Tracker Charts
Long-Term Trend Analysis Charts

See also:

List of Reports


Adapting Exl-Plan for Different Business Types

This topic offers suggestions for adapting Exl-Plan to suit manufacturing, distribution, services or hybrid or multi-site business types. For illustrative purposes, the £ currency symbol and 000s denomination have been used.

As it refers specifically to Exl-Plan Super, certain items may not fully apply to all other versions of Exl-Plan, as these do not differentiate between stocks of finished goods and stocks of materials (or goods for resale).

See also:

Guidance on Entering Assumptions

Structuring a Model

Model-Building Procedure

How Quik-Plan Handles Assumptions

Using Quik-Plan


Adapting Exl-Plan for Manufacturing Businesses

If appropriate, Sales in Monthly Assumption Report No. 1 can refer to sales values (expressed as £000s) rather than to volumes (as 000s). In this case, enter 1.0 as the Net selling prices throughout Report No. 1 and express the Unit cost of materials/packaging in Report No. 2 as decimal values, for example, enter 0.45 if unit costs are equivalent to 45% of sales.

The units used for Sales and Net selling prices can be reversed from 000s of units & £/unit to single units & £000/unit provided that the Net cost of materials/packaging in Report No. 2 is also expressed as £000/unit instead of as £/unit.

To build up the Cost of sales in Report No. 3 proceed as follows:

1.	Indicate the Desired finished stocks as % salesin Report No. 1.

These stock target percentages should relate to the eventual sales value of the stocks. The indicated stock levels will be revalued at a later stage in the model to correspond to their prime costs on the basis of the cost of materials and other direct costs indicated in Report Nos. 2 & 3. For example, if a business with monthly sales of £100,000 plans to hold finished stocks equivalent to six weeks sales, the indicated stock level is £150,000 (valued at selling prices). However, if the direct cost of manufacture etc. is calculated to be 45% of sales, the value of the stocks will be reduced in Report No. 3 to £67,500. This is the stock value that appears in balance sheets.

2.	Estimate the opening values of Desired finished stocksin Report No. 1. Finished stock targets can include a provision to cover work-in-progress.

Enter volumes, or values, based on the expected sales value (not cost) of the opening Desired finished stock. These estimates are used to project production requirements for the first month.

3.	Enter estimates for the Unit cost of materials/packaging and opening and target Desired stocks of materials/packaging in Report No. 2.

4.	Enter projected Direct manpower, Average payroll cost, Other direct costs and opening values (at prime cost) of Finished stocks in Report No. 3.

From this data, Exl-Plan will derive the Finished goods required and Purchases of materials/packaging in Report No. 2 and Cost of sales in Report No. 3.

As far as practicable, only include variable or semi-variable costs as Other direct costs in Report No. 3. Fixed and general overhead costs specifically related to manufacturing can be entered as Operational overheads in Report No. 4. This Report also contains a provision to enter Freight/distribution costs and Selling expenses expressed as percentages of sales.

See also:

Monthly Assumption Report No. 1 - Sales & Finished Stock Targets

Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales
Monthly Assumption Report No. 4 - Overheads


Adapting Exl-Plan for Distribution Businesses

If appropriate, Sales in Monthly Assumptions Report No. 1 can refer to sales values (expressed as £000s) rather than to volumes (as 000s). In this case, enter 1.0 as Net selling prices throughout Report No. 1 and express the Unit cost of materials/packaging or goods for resale in Report No. 2 as decimal values, for example, enter 0.45 if unit costs are equivalent to 45% of sales.

The units used for Sales and Net selling prices can be reversed from 000s of units & £/unit to single units & £000/unit provided that the Net cost of materials/packaging or goods for resale in Report No. 2 is also expressed as £000/unit instead of as £/unit.

Two alternative approaches to determining Cost of sales are explained below.

The first method can be used if the normal convention within the business is to exclude all expense items from Cost of sales other than those directly connected with the cost of purchased goods for resale. The approach is as follows:

1.	Enter zeros for the Desired finished stocks as % sales and for the opening values of the Desired finished stock in Report No. 1.

2.	Enter appropriate values for the Unit cost of materials/packaging or goods for resale and for the opening and target Desired stocks of materials/packaging & goods for resale in Report No. 2.

3.	Enter zeros throughout Report No. 3.

Use the second method if some direct costs are usually included in the Cost of sales, for example to cover certain variable packaging or processing costs. Proceed as follows:

1.	Indicate the Desired finished goods stocks as % salesin Report No. 1.

These closing stock target percentages should relate to the eventual sales value of the stocks. The indicated stock levels will be revalued at a later stage to correspond to their prime costs on the basis of the cost of the goods for resale and other direct costs indicated in Report Nos. 2 & 3. For example, if a business with monthly sales of £100,000 plans to hold stocks of goods for resale equivalent to six weeks sales, the indicated stock level is £150,000. However, if the direct cost of supplying the goods etc. is calculated to be 45% of sales, the value of the stocks will be reduced to £67,500 in Report No. 3. This is the stock value that appears in balance sheets.

2.	Enter opening values of the Desired finished stocksin Report No. 1.

The units used for Sales and Net selling prices can be reversed from 000s of units & £/unit to single units & £000/unit provided that the Net cost of materials/packaging or goods for resale in Report No. 2 is also expressed as £000/unit instead of as £/unit.

3.	Enter the Unit cost of materials/packaging or goods for resale and the opening & target Desired stocks of materials/packaging & goods for resale in Report No. 2.

4.	Enter any projected Direct manpower, Average payroll cost, Other direct costs and opening values (at prime cost) of Finished stocks in Report No. 3.

From this data, Exl-Plan will derive the Finished goods required and Purchases of materials/packaging & goods for resale in Report No. 2 and Cost of sales in Report No. 3.

As far as practicable, only variable or semi-variable costs should be included as direct costs in Reports Nos. 2 or 3. Variable selling and distribution costs linked to sales (but treated as overheads instead of as components of the Cost of sales) can be entered in Report No. 4.

See also:

Monthly Assumption Report No. 1 - Sales & Finished Stock Targets

Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales
Monthly Assumption Report No. 4 - Overheads


Adapting Exl-Plan for Service Businesses

For a service business, Sales volumes in Monthly Assumptions Report No. 1 could be interpreted as referring to unit sales (in thousands) for items such as time sold, numbers of calls or jobs etc. and Selling prices can be set to indicate job or charge out rates etc.

The units used for Sales and Net selling prices can be reversed from 000s of units & £/unit to single units & £000/unit provided that the Net cost of materials/packaging or goods for resale in Report No. 2 is also expressed as £000/unit instead of as £/unit.

If sales volumes cannot be used, Sales should refer to sales values (in £000s) and 1.0 should be entered as Net selling prices throughout Report No. 1.

If the business is strictly labour-only, proceed as follows:

1.	Enter zeros for the Desired finished stocks as % sales and opening values of the Desired finished stocks in Report No. 1. See comments later on about handling work-in-progress.

2.	Enter zeros for the Unit cost of materials/packaging or goods for resale and for the opening & target Desired stocks of materials/packaging & goods for resale in Report No. 2.

3.	Enter details of the Direct manpower, Average payroll cost and Other direct costs in Report No. 3 and enter zeros for the opening values of the Finished stocks.

If the business involves the supply of some products, materials, parts etc., as well as labour, proceed as follows in order to include these costs in the Cost of sales:

1.	Enter zeros for the Desired finished goods stocks as % sales and opening values of the Desired finished stocks in Report No. 1.

2.	Specify unit costs or decimal amounts for the Unit cost of materials/packaging or goods for resale in Report No. 2. For example, enter 0.25 if service materials etc. are equivalent to 25% of sales, and enter values (if appropriate) for opening and target Desired stocks of materials/packaging & goods for resale elsewhere in Report No. 2.

3.	Enter details of Direct manpower, Average payroll cost and Other direct costs in Report No. 3 and enter zeros for the opening values of the Finished stocks.

To provide for work-in-progress (WIP), include appropriate values within Finished stocks in Reports Nos. 1 & 3 and Unit cost of materials/packaging or goods for resale in Report No. 2. Exl-Plan will automatically calculate the value of purchases of materials etc. needed to accommodate changes in WIP arising from changes in Sales and/or Finished stock targets.

To accommodate changes in WIP in a predominantly labour-only business, values for Direct manpower in Report No. 3 must be manually increased or decreased to take account of any projected changes in WIP and/or sales. For example:

If the sales value of WIP jumps from 25% to 50% of monthly sales, this percentage should be entered for the appropriate month as the Desired finished stocks as % sales in Report No. 1. Once Exl-Plan recalculates, Finished goods required in Report No. 2 will increase accordingly. When projecting Direct manpower in Report No. 3, account must be taken of this increase in requirements as Exl-Plan does not automatically adjust Direct manpower levels.  Any increase in Direct manpower will not alter the Cost of sales but it will result in accelerated cash outflows and changes in stock values (i.e. WIP) in the balance sheets.

See also:

Monthly Assumption Report No. 1 - Sales & Finished Stock Targets

Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales
Monthly Assumption Report No. 4 - Overheads


Adapting Exl-Plan for Hybrid & Multi-site Businesses

For businesses involving mixed trades, the simplest approach might be to assign each trade to a different main sales group and to treat each group as a manufacturing, distribution or services business. Alternatively, treat each trade as a strategic business unit, develop separate Exl-Plan models and consolidate the key outputs for each model as explained in Consolidating Projections.


How Quik-Plan Handles Assumptions - How Quik-Plan Handles Assumptions
How Quik-Plan Handles Assumptions

The following tables explain how Quik-Plan allocates its assumptions to Exl-Plan's Monthly Assumption Reports (Nos. 1 - 7) and Quarterly Assumption Reports.

Note that Quik-Plan automatically carries forward the rates for interest, depreciation, stocks, debtors, creditors and VAT from the first year into the subsequent years.

See also:

Creating a Plan within 15 Minutes

Using Quik-Plan

First Year Assumptions

Variable	Allocation
Average monthly sales	To Sales for the first sales subgroup in Assumption Report No. 1
Desired finished stocks	To Desired finished stocks for first sales group in No. 1
Cost of materials/goods	To Unit cost of materials etc. for the first sales group in No. 2
Target materials/goods stocks	To Desired stocks of materials/packaging or goods for resale in No. 2
Average monthly direct costs	To first Other direct costs category in No. 3
Average monthly overheads	To first item under Administration overheads in No. 4
Opening total cost of fixed assets Accumulated opening depreciation Average depreciation rate	To the first category of fixed assets in No. 5
Planned capital expenditure for year	To Capital expenditure in the fifth month for the first category of fixed assets in No. 5
Average VAT rate for all sales	To Average VAT rate for the first sales group in No. 1
Average VAT rate for all inputs	To the Average VAT rates for purchases of materials/ packaging & goods, non-payroll expenses and capital expenditure in No. 6. The proportion of these inputs liable for VAT is assumed to be 90%
Interval between VAT payments	To the VAT payment month indicator in No. 6. The first VAT payment month is assumed to be the second or third month of the year irrespective of the interval used
Interest rate for cash balances	To Cash at bank rate in No. 6
Interest rate for overdraft/loans	To Overdraft and longterm debt rates in No. 7
Opening cash balance Opening longterm loans	To opening monthly balance sheet
Net change in longterm loans in yr	Assigned to the fifth month for Increases in longterm debt in No. 6
Expected grant receipts for year	To apply in the fifth month to Revenue grants receivable in No. 6
Opening trade & other debtors Opening trade & other creditors	To opening monthly balance sheet. Receipts and payments are phased out in the table for Phasing out of opening balance sheet items in No. 7 on basis of 40% during the first month; 30% in the second month; 20% in the third month; and 10% in the fourth month
Average credit given on sales Average credit taken for materials/goods	Converted to percentage distributions for the first sales group in the table for the Treatment of trade creditors and debtors for current year in No. 7
Expected Corporation Tax rate	To the Effective corporation tax rate in No. 6. Any actual tax payment is made in the second quarter of the second year
Planned dividend for the year	Declared in the twelfth month of the first year in No. 6 and paid during the first quarter of the second year
Opening number of shares issued	To No. of ordinary shares issued & fully-paid at opening balance sheet date in No. 6
Proceeds of new share issues	To Proceeds of share issues in No. 6
Number of new shares issued	To No of ordinary shares issued in No. 6

Assumptions for Following Years

Variable	Allocation
Percent annual change in sales	To Sales for the first sales group in the Quarterly Assumptions Report – Sales & Costs
Cost of materials/goods	To  Matls/pack & goods for resale as % sales for the first main sales group in the Quarterly Assumptions Report – Sales & Costs
% annual change - direct costs	Converted to Direct costs as % sales for labour in the Quarterly Assumptions Report – Sales & Costs
% annual change - overheads	Converted to Overhead costs – administration in the Quarterly Assumptions Report – Sales & Costs
Planned capital expenditure	Assigned to the second quarters in each year. Actual payments are made during the third quarters in the Quarterly Assumptions Report – Other Items
Net change in longterm loans	To Increases in longterm debt or repayments in the second quarters of each year in the Quarterly Assumptions Report – Other Items
Expected grant receipts	To Capital & revenue grants receivable during the second quarters of each year and actually received during the third quarters in the Quarterly Assumptions Report – Other Items
Expected Corporation Tax rate	To Effective corporation tax rate for each year. Payments are made in the second quarter of the following year in the Quarterly Assumptions Report – Other Items
Planned dividends	To Dividends declared in the fourth quarter of each year. Actual payments are made in the first quarter of the following year in the Quarterly Assumptions Report – Other Items
Proceeds from new share issues	To Proceeds of share issues in the Quarterly Assumptions Report – Other Items
Number of new shares issued	To No of ordinary shares issued in the Quarterly Assumptions Report – Other Items


Guidance on Entering Assumptions - Guidance on Entering Assumptions
Guidance on Entering Assumptions

This topic offers detailed guidance on entering assumptions into the Monthly Assumption Reports, opening Balance Sheet, prior-year P&L and the Quarterly Assumption Reports. For illustrative purposes, the £ currency symbol and 000s denomination have been used. As this guidance refers specifically to Exl-Plan Super, some items may not be fully applicable to other versions.

Exl-Plan contains many variables and features that may not be required by users. These can be left blank or with zero values as Exl-Plan runs satisfactorily with only minimal assumptions relating to sales, costs, etc., together with an opening balance sheet.

The entry of assumptions for the main sales groups in Exl-Plan Super is entirely optional. Careful consideration should be given to the most effective use of these groups as they provide the basis not only for sales forecasts but also for calculating direct costs, stock levels, VAT, debtors/creditors etc.

Assumptions should be entered only in rows containing ->> or <<->> arrow signs. The <<->> arrows in several of the Monthly Assumption Reports for Exl-Plan Super indicate that values entered in column B can be used to create delay between expensing items and their related cash outflows. Do this by entering zeros to signify immediate cash payments or integers from 1 and 4 to represent credit periods of between one and four months.

With the exception of opening debtors and creditors in the opening balance sheet, all assumption values should be entered net of VAT.

When entering payroll costs always include employees' income tax (PAYE etc.) and related social insurance/security items (PRSI, NIC etc.) which are usually payable a month in arrears to the State (or other similar organisations).

This topic augments the Glossary of Terms which lists the variables used in the Monthly Assumption Reports and provides additional guidance on entering assumptions and explanations of formulae.

See also:

Starting to Use Exl-Plan

Structuring a Model

Adapting Exl-Plan to Different Business Types

Model-Building Procedure

Refer to the following for detailed guidance:

Monthly Assumption Report No. 1 - Sales & Finished Stock Targets

Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales
Monthly Assumption Report No. 4 - Overheads

Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants


Monthly Assumption Report No. 1 - Sales & Finished Stock Targets

Refer to Adapting Exl-Plan to Different Business Types for guidance on accommodating manufacturing, distribution and service businesses.

Seasonal Index
If the business is not seasonal, enter 1.00 as a constant factor for each month or simply ignore this row which is used in conjunction with the *Assumptions | Seasonal Assumptions menu command.

If the business is seasonal, enter monthly values corresponding to the expected pattern of sales. For example, if sales are closely related to the number of work days per month, enter the appropriate number of days for each month and use the *Assumptions | Seasonal Assumptions menu command to allocate estimated annual sales to each month on the basis of this distribution.

Sales (000 units or £000):
Net selling prices (£/unit):
The identities of the main sales groups can be changed by accessing Exl-Plan's Basic Information dialog (*Setup | Enter Basic Model Info) and entering appropriate names. This dialog is also used to enter the name and form of the business for which the projections are being prepared; the start month and year of the projections; and the currency symbol and denomination.

Enter descriptions and monthly projections for the product subgroups.  The subgroups within each main group must be closely related to each other as only the totals for each main group are used by Exl-Plan to calculate VAT, purchases of materials/packaging/ goods, stock levels, debtors & creditors, total direct costs, sales group profitability etc.

When entering sales data, the model offers the option of entering sales volumes or turnover values for the subgroups within each main group. If volumes are being used for the subgroups within a particular group, the Net selling prices should reflect the weighted-average selling price for the group. If sales values are being used, enter £1.00 throughout as the notional Net selling prices for the group.  It is permissible to use volumes for one group and values for another group, provided this is done in a consistent way throughout the model.

The units used for Sales and Net selling prices can be reversed from 000s units & £/unit to single units & £000/unit provided that the Unit cost of materials/ packaging in Report No. 2 is also expressed as £000/unit instead of as £/unit.

When entering Sales or Net selling prices, exclude VAT and deduct any discounts allowable.

Of all the assumptions entered into Exl-Plan, the sales forecasts could have the greatest impact on the reliability of overall projections. Consider using Exl-Plan's sensitivity-analysis facility to explore best, most likely and worst cases using different values for sales volumes and/or prices.

Total sales (£000):
Based on sales volumes * selling prices or, if volumes were not used, sales values.

Bad debts as % sales
Enter an appropriate percentage. Sales receipts and VAT will be automatically adjusted to take account of the bad debt percentage. The cost of bad debts is treated as a charge in the P&L accounts.

Average VAT rates on sales (%):
When entering the VAT rates, different rates can be used for each main sales group. If necessary, composite or weighted average rates can be used for each group to take account of mixed VAT rates, exports etc. The rates used may not correspond to official rates.

Desired finished stocks as % sales for X months ahead:
For each month, enter the desired monthly finished stock levels expressed as percentages of projected sales. For example, if target finished stock levels (when valued at sales price) should be equivalent to one month's sales, enter 100%. In the table immediately below, these targets will be converted into stock levels, expressed as either units or valued at selling prices. Subsequently, these stocks will be devalued to their prime cost in Monthly Assumptions Report No. 3. See Adapting Exl-Plan to Different Business Types for a more detailed explanation of this process.

Use the cells to the left of the <<->> arrows, to link desired stock levels to projected sales for the current month (Enter 0 in these cells) or for 1, 2, 3 or 4 months ahead (Enter 1, 2, 3, or 4 respectively).  This allows stock levels to be linked to projected sales to facilitate stock-building ahead of rising demand, etc. or to accommodate seasonal fluctuations in sales. For example, if a 2 is used and 150% is entered across the row, the sales value of stocks for each month will be set at 1½ times the level of sales projected for two months ahead.

The interactions arising from the use of stock targets can be very complex and may produce results that are mathematically correct but confusing, unexpected or undesired. For example, if sales are fluctuating significantly and stock targets are linked to future sales values, then Desired finished stocks (in this Report) and Finished goods required (in Report No. 2) could vary over a huge range of values. They could even generate negative values for Finished goods required or Purchases of materials/packaging or goods for resale (when *Tools | Calculate & Check is used an error message will indicate this problem).

The ideal solution is to systematically fine-tune the percentages or sales-ahead factors (1, 2, 3 or 4) used in Desired finished stocks as % sales one month at a time while monitoring calculated values for Desired finished stocks, Finished goods required and Purchases of materials/packaging or goods for resale. If these interactions become too complex, a much simpler solution might be to ignore finished stock targets etc. and to include values for finished stocks within the projected values for Desired stocks of materials/packaging or goods for resale (Report No. 2). This simplification may not cause any significant errors for many businesses.

Desired finished stocks (000 units or equivalent sales value in £000):
Enter opening values based on volumes or equivalent selling prices. These should tie in with the opening values (at prime cost) of Finished stocks in Report No. 3.  They are used to help calculate Finished goods required for the first month (in Report No. 2). If precise values are not available, make provisional estimates that can be revised at a later stage when their impact on Finished goods required becomes more evident.

Other Monthly Assumption Reports:

Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales
Monthly Assumption Report No. 4 - Overheads

Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants

Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors


Monthly Assumption Report No. 2 - Cost of Materials/Goods, Stocks & Purchases

Refer to Adapting Exl-Plan to Different Business Types for guidance on accommodating manufacturing, distribution and service businesses.

Seasonal Index
If the business is not seasonal, enter 1.00 as a constant factor for each month or ignore this row which is used in conjunction with the *Assumptions | Seasonal Assumptions menu command.

If the business is seasonal, enter monthly values to correspond with the expected pattern of costs. For example, if sales are closely related to the number of work days per month, enter the appropriate number of days for each month and use the *Assumptions | Seasonal Assumptions menu command to allocate an estimated annual cost to each month on the basis of this distribution.

Finished goods required (000 units or equivalent sales value in £000):
Derived from Total sales and Desired finished stocks in Report No. 1.

Unit cost of materials/packaging or goods for resale (£/unit or £0.xx if units not used):
Enter unit costs. If sales volumes are not being utilised, enter unit costs as decimal amounts, e.g. £0.50, based on the notional selling price of £1.00 per unit that should have been previously specified in Report No. 1.

Cost of materials/packaging or goods required:
Based on calculated results for Finished Goods Required and the entered Unit Costs of Materials.

Desired stocks of materials/packaging or goods for resale (£000):
Enter opening values in the first column and target monthly levels in the following columns. The opening total for the main sales groups will be automatically incorporated in Exl-Plan's opening balance sheet. Consequently, this total should be the same as that which appears in the published or projected opening balance sheet for the business.

Purchases of materials/packaging & goods for resale
Based on: Finished goods required + closing Material stocks - opening Material stocks.

Other Monthly Assumption Reports:

Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales
Monthly Assumption Report No. 4 - Overheads

Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants

Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors


Monthly Assumption Report No. 3 - Direct Manpower, Other Direct Costs & Cost of Sales

Refer to Adapting Exl-Plan to Different Business Types for guidance on accommodating manufacturing, distribution and service businesses.

Try to include only directly attributable variable or semi-variable costs as distinct from fixed costs in this report. All costs entered into this report will be included in the Cost of sales in Report No. 3. Assumptions made for Direct manpower and Other Direct costs should be related to the values or quantities of Finished goods required as calculated in Report No. 2 and should not be based solely on projected Total sales in Report No. 1.

Seasonal Index
If the business is not seasonal, enter 1.00 as a constant factor for each month or ignore this row which is used in conjunction with the *Assumptions | Seasonal Assumptions menu command.

If the business is seasonal, enter monthly values to correspond with the expected pattern of costs. For example, if these costs are closely related to the number of work days per month, enter the appropriate number of days for each month and use the *Assumptions | Seasonal Assumptions menu command to allocate the estimated annual cost to each month on the basis of this distribution.

Direct manpower (Persons):
Enter projected numbers of direct staff. If it is completely impracticable to allocate manpower between the main sales groups, enter the estimated total direct employment opposite the first main sales group but bear in mind that this will distort the gross profit analysis in the Monthly Performance Review Report.

If it is not possible to enter any estimated numbers for projected Direct manpower, enter the estimated total monthly payroll cost (as £'s and not as £000's) for each sales group in the rows designated for Direct manpower and then set the Average payroll cost to "1"  (i.e. 10 persons at £1,000 per month equates to 1 at £10,000 per month.). If this approach is used, the calculation of total employment numbers in the Monthly Performance Review Report will be incorrect.

Sales/output per direct person:
These productivity measures refer to sales or output values, or to units and equivalent sales values, depending on the basis used to enter assumptions for Sales and Finished stock targets specified in reports Nos. 1 and 2.

Average payroll cost (£000/pers/mth)
Use a weighted average monthly payroll cost. This should include employees' income tax (PAYE) and related items, such as social security/insurance (e.g. NIC & PRSI), payable to the State (or other organisation) a month in arrears.

Other direct costs (£000):
Insert values  (0, 1, 2, 3 or 4) under the "X" to defer the payments related to these costs by up to four months. The supplied variable descriptions that are unprotected can be altered or ignored. Note that any revised descriptions will be automatically changed in the Monthly Cashflows.

Allocation of other direct costs on the basis of direct payroll costs (or sales) (£000):
For this allocation to work as indicated, Direct manpower levels should have been entered for each of the main sales groups otherwise Other direct costs will be allocated between the main sales groups in proportion to their respective sales.

If neither Direct manpower nor Sales are projected for a particular month, Other direct costs will be assigned equally to the main sales groups.

If no Direct manpower levels were specified but it is desired to allocate Other direct costs between groups on a different basis, simply enter notional Direct manpower levels which reflect the desired allocation and set the Average payroll cost to zero for each month. If this approach is used, the calculation of total employment numbers in the Monthly Performance Review Report will be incorrect.

Direct cost per unit (£/unit or £0.xx if units not used):
For each month, Exl-Plan calculates monthly unit costs for each main sales group. If sales values have been used instead of sales volumes, the unit costs are expressed as decimal amounts, e.g. as £0.50, based on a notional selling price of £1.

The projected unit costs in this table should not vary significantly from one month to another. If the differences are substantial, check the cost assumptions used in Report No. 3 and review the volatility of projected Total sales and Desired finished stocks in Report No. 1 and Finished goods required in Report No. 2.

Finished stocks (£000):
Enter opening values for each group in the first column. The opening total for the main sales groups will be automatically incorporated in Exl-Plan's opening balance sheet. Accordingly, this total should be identical to that appearing in the published or projected opening balance sheet for the business.

The Direct cost per unit, calculated in the previous table, is used in conjunction with the Desired finished stocks (in Report No. 1) to calculate the projected monthly values for Finished stocks (based on total prime costs) given in this table.

The interactions between stock levels, production requirements and direct costs can be complex especially if these are fluctuating independently of each other and vary from month to month. If there is an unexpected jump in Finished goods required (in Monthly Assumptions Report No. 2) for the first month of projections, review assumptions for the opening values of Desired finished stocks (Report No. 1). These should be based on selling prices and not on cost values. To achieve a smooth transition between stock values for the beginning and end of the first month, use the first month's projected Direct cost per unit (in Monthly Assumption Report No. 3) to inflate or deflate the sales values of opening Finished stocks (in Report No. 1).

Large fluctuation in monthly gross profit margins may arise if percentage targets have been set for Desired finished stocks (in Report No. 1) and if projected monthly sales vary widely from month to month. These fluctuations are most likely to be caused by a failure to adjust Direct labour and Other direct costs in Monthly Assumption Report No. 3 to move in step with the levels of Finished goods required as calculated in Monthly Assumption Report No. 2. To solve this problem, adjust the Direct labour and Other direct costs in line with calculated requirements where it is realistic to assume that these are, in practice, fully (or partly) variable. This should smooth out the fluctuations in gross margins. If this approach is not feasible, a much simpler approach is to set the percentage targets for the Desired finished goods and their opening values to zero (in Report No. 1) and increase Raw material stocks (in Report No. 2) to reflect all stock movements.

Cost of Sales:
Based on: Total direct costs + opening Finished stocks -closing Finished stocks.

Other Monthly Assumption Reports:

Monthly Assumption Report No. 4 - Overheads

Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants

Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors


Monthly Assumption Report No. 4 - Overheads

Insert values (1, 2, 3 or 4) underneath the "X's" in column B within this report to defer the actual cash payments by up to four months. Enter zeros where payments are made within the month.

Any supplied variable descriptions, which are unprotected, can be altered or left empty. Note that any changed descriptions will be automatically updated in the Monthly Cashflows.

Seasonal Index
If the business is not seasonal, enter 1.00 as a constant factor for each month or ignore this row which is used in conjunction with the *Assumptions | Seasonal Assumptions menu command. If the business is seasonal, enter monthly values to correspond with the expected pattern of costs.

If, for example, costs are closely related to the number of work days per month, enter the appropriate number of days for each month and use the *Assumptions | Seasonal Assumptions menu command to allocate the estimated annual cost to each month on the basis of this distribution.

Operational overheads (£000):
Include all fixed and indirect operating costs, not already included in Report No. 3.

Selling & distribution costs (£000):
This cost group includes two sets of variable selling expenses expressed as percentages of monthly sales for the current month or for up to four months ahead. To use these, enter costs as percentages of sales for up to four months ahead and insert 0, 1, 2, 3 or 4 in the appropriate cells immediately under the "X" to express these costs as percentages of sales for the current (0) or subsequent (1-4) months.

Use the unlocked variables further down to handle fixed costs. Enter 0,1,2,3 or 4 under the "X" to secure up to four months credit on these costs.

Indirect payroll (£000/pers/mth)
Supervision payroll (£000/pers/mth)
Sales payroll (£000/pers/mth)
Clerical payroll (£000/pers/mth)
Management payroll (£000/pers/mth)
Staff payroll (£000/pers/mth)
If it is not feasible to enter projected staff numbers with projected average payroll costs, use "1" as the total staff number. Then, enter a combined total payroll cost instead of an average individual payroll cost (this works on the basis that 10 staff at £2,000 per month equate to 1 at £20,000 per month). If this approach is used, the calculation of total employment numbers in the Monthly Performance Review Report will be incorrect.

Administration overheads (£000):
Establish & general costs (£000):
The cost descriptions within these groups can be changed. Enter 0,1,2,3 or 4 below the "X" to take credit of up to four months on these items.

Other Monthly Assumption Reports:

Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants

Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors


Monthly Assumption Report No. 5 - Fixed Assets, Capex, Leasing & Capital Grants

This report handles four categories of fixed assets. The supplied initial categories - land & buildings, plant & machinery, computers & equipment and motor vehicles - can be changed by the user. Total values for the four categories are contained in a summary table at the end of the report. The combined total opening cost less accumulated depreciation for the four categories is used as the total opening value of fixed assets in Exl-Plan's opening balance sheet. This opening total value should be identical to that appearing in the published or projected opening balance sheet for the business.

Cost or valuation (£000)
Accumulated depreciation (£000)
Base depreciation on original cost or use double declining balance
Depreciation & grant amortisation rate (% pa)
Enter (a) original cost or up-to-date valuation as at the opening balance sheet date, (b) accumulated depreciation as at the opening balance sheet date and (c) current-year annual depreciation rate. Indicate whether depreciation should be based on the straight-line method applied to the original cost (enter "0" - zero) or the double-declining-balance method for accelerated depreciation (enter "1" - digit one).

Capital expenditure (£000)
Enter a value (0, 1, 2, 3 or 4 as appropriate) under the "X" to provide for up to four months of credit on any planned capital expenditure.

% Capital expenditure eligible for grants
Capital grant rate as % eligible fixed assets
These items refer to Government, local authority and other capital grants linked to fixed asset purchases and repayable only in exceptional circumstances. Non-repayable operating grants and subsidies should be treated as revenue items and entered in Report No. 6.

These percentages refer to projected capital expenditure and apply across the twelve months. They indicate the proportion of capital expenditure likely to be eligible for capital grants and the corresponding grant rate applicable to the eligible expenditure. For example, if 60% of total capital expenditure is eligible for capital grants at a rate of 25%, grants will be equivalent to 15% of total expenditure. Enter a value (0, 1, 2, 3 or 4 as appropriate) under the "X" to indicate grant payment delays of up to four months.

Total capital grants (£000)
Accumulated grant amortisation (£000)
Enter opening values in the leftmost column. The total opening value of capital grants for all four categories, less the total opening value for amortised grants, is automatically included as a contingent long-term liability in Exl-Plan's opening balance sheet. This total should be the same as that contained in the business's published or projected opening balance sheet.

New finance leases (£000)
New finance lease charges (£000)
New finance lease capital payments (£000)
These entries should refer to new finance and capital leases only.  Projections relating to any existing finance leases should be entered in Report No. 6.  The capital values of newly leased items are automatically included in the fixed asset totals.

Sale of fixed assets (£000):
Enter details of the proceeds, costs and related accumulated depreciation. Profits/losses derived from the sale of fixed assets are included in the P&L accounts. Fixed asset and depreciation balances are automatically reduced to take account of fixed asset sales.

Other Monthly Assumption Reports:

Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors

Opening Balance Sheet


Monthly Assumption Report No. 6 - Funding, Interest Rates, VAT & Related Items

Interest rates (% pa):
-Cash at bank
-Overdraft
-Long-term debt
Interest payable on 'other loans' (£000)
Increases in long-term debt (£000)
Long-term debt repayments (£000)
Increases in 'other loans' (£000)
'Other loan' repayments (£000)
The principal difference between Long-term debt and Other loans is that interest payable on the former is calculated automatically by Exl-Plan from the interest rate specified for long-term debt whereas interest for the latter is entered directly by the user as an absolute amount at Interest payable on other loans.

When calculating Overdraft interest, the model applies the specified overdraft interest rate to the previous month's closing overdraft (if any) plus 50% of the current month's pre-interest cashflow (if this negative).

To calculate interest on Cash at bank, the model uses the specified interest rate and the previous month's closing cash balance (if any) plus 50% of the current month's pre-interest cashflow (if this is positive). Once interest for the month has been calculated, any positive net cashflow for the month is used to reduce any existing Overdraft and the balance is then treated as Cash at bank. If the net cashflow is negative, it is used to reduce any Cash at bank and/or added to any existing Overdraft.

Long-term debt interest is based on the specified Long-term debt interest rate and the previous month's closing balance for any Long-term debt plus half the net change for the month arising from any repayment or increase in the balance outstanding.

Finance leases from prior years (£000):
These refer to existing finance leases taken out in previous years. Details of newly created finance leases for the current year should be entered in Assumption Report No. 5.

Total long-term debt (£000)
Total other loans (£000)
Total finance leases (£000)
Proportions payable within one year:
Estimate approximate percentages so that liabilities for these loans etc. can be allocated between current and long-term liabilities in the projected balance sheets. When using this facility, account must be taken of any repayments anticipated during the second year as well as any increases in loans likely to arise during the current year.

To ignore this facility and treat all liabilities for loans etc. as long-term liabilities, enter zeros throughout.

Miscellaneous income (£000)
This is credited to the monthly P&L. Consider whether it should be entered gross or net of any taxes.

Revenue grants receivable (£000)
These refer to non-repayable operational, employment or training grants and related subsidies. Enter a value (0, 1, 2, 3 or 4) in the cell under the "X" to defer the actual receipt of grants by up to four months.

Operating lease & HP payments (£000)
These should exclude finance and capital leases, which should be entered into Report No. 5.

Intangible asset write-downs (£000)
Enter the amounts by which these assets should be written down. Use a negative number to increase the value of intangible assets.

Changes (+/-) in accruals (£000)
Changes (+/-) in prepayments (£000)
These two rows enable adjustments to be made to the projected cashflows and balance sheets to take account of prepayments and accrued expenses. It is not essential to use this feature unless it is desired to make specific adjustments in order to improve the accuracy of the cashflow projections or to accommodate substantial cash movements, which cannot be handled elsewhere.

Approximate percent of total payroll costs relating to income taxes & social insurances for all staff (%)
These costs refer to income taxes (e.g. pay-as-you-earn) and related social insurance charges (e.g. NIC, PRSI etc.) usually paid by the employer to the State within a month of net wages/salaries being paid to employees.

Enter approximate percentages. These are likely to be in the range 30-50% and can be derived from recent income tax (PAYE) returns. In the Monthly Cashflow Projections, income taxes and social insurances are automatically paid one month in arrears of salaries and wages. For example, if the total payroll for a particular month is £20,000 and the percentage for income taxes etc. is estimated at 30%, then £14,000 will be paid as net salaries to staff during that month and £6,000 will be paid to the State during the following month.

Calculate VAT on an invoice or cash-received basis?
Enter "0" (zero) to indicate invoice basis or "1" (digit one) for cash-received basis.

Mark months when VAT is paid to State
If the business pays VAT on a two-month cycle, enter a " 1" (digit one) every second month.  Enter a "1" every third month for a three-month cycle. All other months should contain zeros.

Average VAT rates for purchases of materials/packaging & goods (%)
Percent of all non-payroll expenses (overheads, HP etc.) subject to VAT (%)
Average VAT rate for non-pay expenses (%)
Percent of capital expenditure, finance lease payments and charges subject to VAT (%)
Average VAT rate for capital expenditure, finance lease payments and charges (%)
Estimate approximate percentages and rates. Use weighted average rates to take account of expense categories using different VAT rates.  For most businesses, the percentage of non-payroll expenses subject to VAT are likely to exceed 90% as VAT is payable on almost all types of expenses other than rent, insurance etc. The entered VAT rates need not coincide with official published rates.

Proceeds of share issues (£000)
Enter amounts net of expenses and commissions.

No. of new ordinary shares issued (000s)
No. of ordinary shares issued & fully paid at opening balance sheet date (000s)
These numbers are used to calculate share-based values such as earnings per share etc. in the Quarterly and Annual Performance Review Reports.

Effective corporation tax rate for year (%)
Enter an effective tax rate after taking account of deferred tax, credits, ACT etc. Corporation tax liabilities relating to previous years but payable during the current year are handled in Assumption Report No. 7 within the table for phasing out opening balances.

Ignore this item if the business is a sole trader or partnership.

Dividends for the current year (£000)
Enter a value (0, 1, 2, 3 or 4) in the cell under the "X" to defer the payment of dividends declared in the current year by up to four months. Dividends declared in previous years, but payable during the current year, are handled in Assumption Report No. 7 within the table for phasing out opening balances.

If the business is a sole trader or partnership this item changes automatically to Drawings (£000) and the following two rows facilitate the entry of income tax liabilities and payments.

Income tax provisions (£000)
Income tax payments (£000)
Enter estimated amounts. This item does not apply when Exl-Plan has been set up for a company.

Other Monthly Assumption Reports:

Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors

Opening Balance Sheet


Monthly Assumption Report No. 7 - Opening Balance Sheet Items, Creditors & Debtors

This report contains two tables for phasing out specific opening balance sheet items and handling current-year creditors and trade debtors respectively.  Avoid creating calculation ERRORS when completing these tables.

Phasing out of opening balance sheet items
The total amount to be phased out for each item is indicated in the left-hand column of the table. This is the value (if any) previously entered into Exl-Plan's opening balance sheet. (If no values have yet been entered in the opening balance sheet, the totals in this table will be zero until values have been entered into the opening balance sheet and Exl-Plan recalculates).

The total amount to be phased out during the current year for each item must be the same as the total in the left-hand column. This total will have been entered already, or has still to be entered, into the opening balance sheet. Otherwise, a calculation error will arise. This error will be reported when Exl-Plan next recalculates and the item causing the error will be identified by ERR appearing in lieu of an ->> arrow in column B.

Treatment of trade creditors & debtors for current year
Use the six columns in this table to enter the percentage distributions of credit expected to apply during the current year to the projected sales and purchases of materials/packaging & goods for each of the main sales groups. The total for each row must always equal 100%; otherwise an ERROR will be displayed in the right-hand column and will be reported when Exl-Plan next recalculates.

The following table shows how this method of allocating credit works in practice:

In this example, eighty percent of Group 1 debtors are expected to pay cash or settle within one month and the remaining twenty percent are expected to pay during the second month. The average credit taken works out at 21 days. In the case of Group 2, one-fifth are expected to pay within one month, a further fifth are projected to pay within one to two months and so on. The average credit is about 75 days.

Other Assumption Reports:

Opening Balance Sheet

Prior Year P&L

Assumptions for 2nd & 3rd Years and Projections for 1st Years - Sales & Costs


Opening Balance Sheet

To complete the entry of assumptions for the first twelve months, all outstanding values must be entered into the opening balance sheet. This is accessed via the *Output | Monthly Balances menu command. Once these values have been entered, recalculate (using *Tools | Check All & Calculate to confirm that the opening balance sheet balances and that there are no calculation errors in the model.

If using Exl-Plan Pro or Super, note that:

1.	Values for the opening fixed assets, accumulated depreciation and opening government grants within the opening monthly balance sheet are picked up from entries in column F in Monthly Assumptions Report No. 5.

2.	Values for total opening finished goods and material stocks are secured from column F in Monthly Assumption Reports Nos. 1 and 2 respectively.

Monthly Assumption Report No. 7 should be used to phase out the opening balances for certain items that appear in the opening balance sheet. If the totals being phased out differ from the opening balances, Exl-Plan will report an error when it next recalculates using the *Tools | Calculate & Check menu command. If this happens, return to Report No. 7 and revise the values in rows showing ERR instead of arrows in column B.

Other Assumption Reports:

Prior Year P&L


Prior Year P&L

If desired, a P&L account for the year preceding the projections can be entered alongside the projected P&Ls. Access the prior year's P&L via the *Output | Profit/Loss menu command. This P&L is used within Exl-Plan's First-year and Textual Summary Reports to compare the full-year projections with the actuals for the previous year. If final full-year figures are unavailable, estimates can be used.

Other Assumption Reports:

Opening Balance Sheet


Assumptions for 2nd & 3rd Years and Projections for 1st Year - Sales & Costs

This report incorporates quarterly projections for the first year derived from the monthly assumptions previously entered. These offer guidance on the first-year trends to assist the preparation of forecasts for the next eight quarters.

All monthly assumptions should be entered (and checked) and the model recalculated to update projections for the initial four quarters before entering assumptions for the second and third years.

Enter assumptions for the last eight quarters to the right of rows with ->> arrows.

Sales (000s units or £000):
- New products/services in 2nd & 3rd years
The units used for Sales and Net selling prices can be reversed from 000s units & £/unit to single units & £000. Provides for the introduction of sales forecasts for an additional main sales group for the 2nd and 3rd years.

Net selling prices (£/unit):
The units used for Sales and Net selling prices can be reversed from 000s units & £/unit to single units & £000. When entering Sales or Net selling prices, exclude VAT and deduct any discounts allowable.

Matls/pack & goods for resale as % sales:
These percentages should embrace all the cost categories included in Monthly Assumption Report No. 2.

Direct costs as % sales:
These percentages should embrace all the cost and expense categories included in Monthly Assumption Report No. 3.

Overhead costs (£000):
The projected costs should embrace all the cost and expense categories in Monthly Assumption Report No. 4.

Other Assumption Reports:

Assumptions for 2nd & 3rd Years and Projections for 1st Years - Other Items

4th & 5th Year Assumptions


Assumptions for 2nd & 3rd years and Projections for 1st Year - Other Items

Annualised sales per employee (£000)
Estimate for the purpose of forecasting employment numbers using the first-year forecasts as guides.

Dividends declared (£000)
Dividend payments (£000)
Separate values must be entered for dividend declarations and payments. Payment details in the second year should include any dividends declared during the first year but not scheduled for payment until the second year.

If the business is a sole trader or partnership, these items automatically change to Drawings (£000) and Miscellaneous opening payables (£000).

Effective corporation tax rate (%)
Corporation tax payments (£000)
Enter an effective rate that takes account of allowances etc. Exl-Plan assumes that Corporation Tax is paid during the second quarter after a financial year-end.

If the business is a sole trader or partnership, these items automatically change to Income tax provision for year (£000) and Income tax payments (£000).

Composite depreciation rate (%)
For second and subsequent years, depreciation is calculated on a straight line basis. Use the first year's calculated values as guides. Take account of the possible impact of having used the double-declining-balance method during the first year.

Capex - excluding finance leases (£000)
Capex payments (£000)
These rows accommodate any differences in the timing of capital expenditure and the actual payments made to suppliers. Include payments due for any capital expenditure incurred but not actually paid for during the first year.

Capital & revenue grants received (£000)
This row should be used to accommodate any unpaid grants relating to the first year as well as projected grant receipts during the second and third years.

Trade debtor days
Stock days
Trade creditor days
Use the first year values as guides. Exclude VAT from sales when calculating Stock days. Include VAT relating to creditors and debtors when estimating the Trade creditor days and Trade debtor days. If the VAT rate is projected to change during the second or third year, make proportionate adjustments to the forecasts for debtor and creditor days.

Effective VAT rate on sales (%)
Effective VAT rate on inputs (%)
Use the first year values as guides. These rates should be based on total sales and total inputs before taking account of any non-vatable items. Consequently, they will not necessarily correspond to any official VAT rates.

Other Assumption Reports:

4th & 5th Year Assumptions


4th & 5th Year Assumptions

These assumptions only apply to Exl-Plan Pro or Super. Complete the second and third-year projections before embarking on the fourth and fifth year projections and use the calculated values for the second and third years as guides. Note that the projections made for the fourth quarter of the third year are automatically carried forward into the fourth and fifth years for the following items:

Interest rates
Allocation of loans between long and short-term
Capital grant amortisation rate
Composite depreciation rate
Effective VAT rates on sales and inputs.


Calculation Error Messages - Calculation Error Messages
Calculation Error Messages

Exl-Plan was extensively tested during its development to ensure that it is robust and does not contain or cause any errors. It was tested with zero values throughout to eliminate divide by zero errors; with large values for every assumption to test for possible imbalances between the sub-models; and with values entered and calculated on a progressive basis to detect errors caused by the interaction between variables.  All calculations and formulae were checked manually and the entire model was extensively tested using an auditing system.

Invest-Tech cannot guarantee that all possible errors or sources of error have been removed and cannot anticipate errors that may be introduced by users.  Accordingly, users should always review the detailed output reports for possible anomalies and calculation errors.  Note that in some circumstances, values may not appear to add up correctly due to rounding off or when Exl-Plan's sensitivity-analysis facility is being used.

The following messages will be displayed immediately after Exl-Plan recalculates using the *Tools | Calculate & Check menu command (or "C" button) if a calculation error is detected. Errors will only be reported if outside pre-set tolerance limits as Exl-Plan automatically filters out and ignores trivial errors due to rounding off and calculation differences etc. arising after the third decimal place.

In addition, when Exl-Plan detects a calculation error, it opens a help procedure window describing the error and explaining how it can be resolved. This facility can be turned on and off using *Setup | Toggle Help for Calculation Errors.

The explanations given below of errors relate to the Super version of Exl-Plan.

See also:

Avoiding Calculation Errors

Error messages:

All percentage distributions of credit are not equal to 100%!

Error in the phasing out of opening balance sheet items!

Negative Finished Goods Required have been encountered!

Negative Purchases of Materials/Packaging have been encountered!

One or more monthly balance sheets for the first year are unbalanced!

One or more quarterly balance sheets for second or third years are unbalanced!

Opening monthly balance sheet is unbalanced!

Some formulae are generating errors!

The four sub-models are not producing identical results!

All percentage distributions of credit are not equal to 100%!

Error in the phasing out of opening balance sheet items!

Negative Finished Goods Required have been encountered!

Negative Purchases of Materials/Packaging have been encountered!

One or more monthly balance sheets for the first year are unbalanced!

One or more quarterly balance sheets for second or third years are unbalanced!

Opening monthly balance sheet is unbalanced!

Some formulae are generating errors!

The four sub-models are not producing identical results!


All percentage distributions of credit are not equal to 100%!

Go to Monthly Assumption Report No. 7 and locate the row containing an ERROR message in the right-hand column of the table for the Treatment of trade creditors & debtors for the current year. This message signifies that the percentage distribution of credit for the row does not equal 100%.  Change the percentages used.


Error in the phasing out of opening balance sheet items!

Locate the error within the first table in Monthly Assumption Report No. 7.  Go to the row indicating ERR instead of an ->> arrow in column B. This signifies that the total amount being phased out for an item (row) over the twelve months differs from its opening value in the opening balance sheet.  Check and change appropriate entries.


Negative Finished Goods Required have been encountered!

Negative values have been detected for Finished goods required (Report No. 2). These could produce unexpected, and undesired, results in the Monthly Cashflow Projections etc. In real life, negative values are not possible, so review the monthly targets for Finished stocks (Monthly Assumptions Report No. 1).


Negative Purchases of Materials/Packaging have been encountered!

Negative values have been detected for Purchases of materials/packaging or goods for resale (Report No. 2). These could produce unexpected, and undesired, results in the Monthly Cashflow Projections etc. In real life, negative values are not possible, so review the monthly targets for Stocks of materials/packaging or goods for resale (Report No. 2).


One or more monthly balance sheets for the first year are unbalanced!

Refer to Check balance sheet balances at the bottom of Monthly Balance Sheets to determine the magnitude and direction of the balance sheet error. Determine whether the value of the error increases each month or remains constant. This can provide a clue to its source. Try to locate the source by progressively removing assumption values and recalculating using the *Tools | Calculate & Check menu command or "C" button. If the error's source cannot be located or easily fixed, the best option is often to revert to an error-free backup copy of the model.


One or more quarterly balance sheets for second or third years are unbalanced!

Refer to Check balance sheet balances at the bottom of Quarterly Balance Sheets to determine the magnitude and direction of the balance sheet error. Determine whether the value of the error increases each quarter or remains constant. This can provide a clue to its source. Try to locate the source by progressively removing assumption values and recalculating using the *Tools | Calculate & Check menu command or "C" button. If the error's source cannot be located or easily fixed, the best option is often to revert to an error-free backup copy of the model.


Opening monthly balance sheet is unbalanced!

Go to the first column of the Monthly Balance Sheets and refer to Check balance sheet balances at the bottom of the opening balance sheet to determine the magnitude and direction of the opening balance sheet error. Particularly examine the opening values for finished stocks, material stocks, fixed assets and capital grants derived from values entered in Monthly Assumption Reports Nos. 2, 3 and 5. To identify the item causing an imbalance, it may be necessary to increase the number of decimal places being displayed.


Some formulae are generating errors!

This may be due to the use of Cut & Paste to move assumption values whose cells are referenced in formulae. If possible use Undo to remove the change. Also, review reports for #DIV/0! and #REF!. If the problem cannot be eliminated, reload an earlier version of the same model and confirm that it is error-free. Instead of using Cut & Paste, use Copy & Paste and then delete the copied item.


The four sub-models are not producing identical results!

Go to the Check Balances Report and from the Differences column of this report identify the sub-model and element causing the difference. If the error cannot be easily located, endeavour to restore the model to its state immediately before the error was detected and then progressively reinsert values in batches while recalculating frequently to track down the source of the error.  Failing this, reload an earlier version of the same model and confirm that it is error-free.


User Registration Form - User Registration Form
User Registration Form

You can print this Form - use File | Print Topic.

Use this Form to register as a user of Exl-Plan, obtain support, secure information on upgrades etc. (BLOCK CAPITALS PLEASE)

Exl-Plan version (circle one):   Micro | Lite |  Pro | Super    Edition (circle one):    UK/Inter'l | US/Canadian

Code Number (refer to top right-hand corner of Front worksheet): _____________________________

Name: ____________________________________________________________________________

Position:	___________________________________________________________________________

Organisation: _______________________________________________________________________

Address:  __________________________________________________________________

___________________________________________________________________________

Telephone: _________________________________ Ext.: ____________________________

Fax: ______________________________________ Email: ___________________________

We wish to register as a user of Exl-Plan Super. It runs on the following version of Excel:  ___________

We first heard about Exl-Plan from: _____________________________________________________

We bought/upgraded Exl-Plan from: ____________________________________________________

Tick this box if you wish to receive details of Invest-Tech's other financial planning software for businesses:    [  ]

Please print and return the Form immediately to:

Invest-Tech Limited, 27 Ardmeen Park, Blackrock, Co Dublin, IRELAND

Fax:  +353-1-2782391

Please feel free to enter any comments, ideas and/or suggestions about Exl-Plan below:


Shareware Upgrade - Shareware Upgrade
Shareware Upgrade

This topic is for users of the shareware version of Exl-Plan who wish to upgrade to the fully-operational commercial version.

Use of the shareware version of Exl-Plan for anything except evaluation is not permitted under the terms of the Shareware License Agreement. If Exl-Plan is suitable to your needs there are many positive benefits of registering including:

♦	Copy of a 125-page spiral-bound manual.

♦	Access to support by phone, fax and e-mail.

♦	Securement of the latest version of Exl-Plan.

♦	Details of upgrades, user tips etc.

Invest-Tech is a company member of the Association of Shareware Professionals (ASP) in the US. If, a problem relating to Exl-Plan cannot be satisfactory resolved through Invest-Tech, a user can approach the ASP Ombudsman - refer to the Association of Shareware Professionals Ombudsman Statement.

Upgrading can be done by phone, mail, fax, WWW or e-mail. See *Exl-Plan Help | View Shareware Upgrade Form.

If the full Code Number (comprising unique 11-13 digit number and version details) at the top right-hand corner of the front screen of Exl-Plan is quoted when upgrading, the shareware copy of Exl-Plan on a user's computer can be remotely upgraded. This means that a user can enter meaningful data into the shareware copy that can be subsequently upgraded to the fully operational version. This saves further downloading and the re-entry of assumptions.

Once payment has been cleared (usually within 24 hours), an Unlock Code will be sent to the user usually by the same method (i.e. post, fax, e-mail etc.) as used to apply for the upgrade. Once the user receives the Unlock Code, the downloaded Exl-Plan shareware file, or a copy containing the original code number, should be loaded and the Unlock Code entered via the *Exl-Plan Help | Enter Unlock Code Number. This copy of Exl-Plan becomes the user's master file. During the unlocking process, a working copy (WORKPLAN.XLS) will be automatically created for day-to-day use. Note that the Unlock Code will not work with any other downloaded Exl-Plan files.

If Invest-Tech determines from the Code Number that a significant new release of Exl-Plan has become available, the upgrading user will be advised and either a disk containing the latest release will be despatched by airmail or the user will be directed to a location for electronic delivery of the latest version.

If Exl-Plan is upgraded remotely by Invest-Tech, the upgraded workbook file should be kept safe and intact as their primary Master Copy of Exl-Plan. Copies of this file should be created and used for normal working purposes. No credit will be given if a master copy of Exl-Plan gets lost and needs to be replaced.

Note: It is impossible to remotely upgrade and simultaneously convert one version or edition of Exl-Plan into another e.g. to convert a shareware copy of Exl-Plan Lite for the United Kingdom into a full version of Pro for the United States.

Optionally, a printed bound manual for Exl-Plan (c. 125 pages) can be purchased when applying to upgrade. This will be despatched by airmail and, depending on the destination, may take from two day to two weeks to be delivered.

To upgrade, use one of the following routes:

1. Built-in Upgrade Form

Complete the Upgrade Form contained in the Upgrade worksheet of Exl-Plan (access via *Exl-Plan Help | View Shareware Upgrade Form. Print and send it to:

Invest-Tech Limited, 27 Ardmeen Park, Blackrock, Co Dublin, IRELAND

Tel: +353-1-283 4083 (24 hour)     Fax: +353-1-278 2391

Email: info@planware.org

2. Invest-Tech's Web Site

Upgrade via Invest-Tech's Web site at:

www.planware.org

This site offers a range of options for upgrading Exl-Plan including secure (SSL) ordering facilities, on-line shopping cart, direct access to fully-operational version, fax/mail order form etc. The following credit cards can be used: VISA, MasterCard, Amex or Discover.

3. Other

See the supplied README file for additional registration routes.

See also:

Commercial Version Licence Agreement

Shareware Version Licence Agreement

Association of Shareware Professionals Ombudsman Statement


Glossary of Terms

Glossary of Terms

The terms in this glossary refer to variables in the Monthly Assumption Reports. The definitions provide additional guidance on entering assumptions and explanations of formulae. They supplement Guidance on Entering Monthly Assumptions.

For illustrative purposes, the £ currency symbol and 000s denomination have been used.

% Capital expenditure eligible for grants

Accumulated depreciation

Accumulated grant amortisation

Allocation of other direct costs on the basis of direct payroll costs

Approximate percent of total payroll costs relating to income taxes & social insurances for all staff

Average payroll cost

Average VAT rate for non-pay expenses

Average VAT rates on sales

Calculate VAT on an invoice or cash-received basis

Capex

Capital expenditure

Capital grant rate as % eligible fixed assets

Changes (+/-) in accruals

Changes (+/-) in prepayments

Clerical payroll

Cost or valuation

Depreciation & grant amortisation rate

Desired finished stocks (000 units or equivalent sales value in £000)

Desired finished stocks as % sales for X months ahead

Desired stocks of materials/packaging or goods for resale

Direct cost per unit

Direct manpower

Dividends for the current year

Effective corporation tax rate for year

Finance leases from prior years

Finished goods required

Finished stocks

Indirect payroll

Interest payable on 'other loans'

Interest rates

Longterm debt

Management payroll

Mark months when VAT is paid to State

Net selling prices

New finance lease capital payments

New finance lease charges

New finance leases

Operational overheads

Other direct costs

Percent of all non-payroll expenses (overheads, HP etc.) subject to VAT

Phasing out of opening balance sheet items

Proportions payable within one year

Revenue grants receivable

Sale of fixed assets

Sales

Sales payroll

Seasonal Index

Selling & distribution costs

Staff payroll

Supervision payroll

Total capital grants

Treatment of trade creditors & debtors for current year

Unit cost of materials/packaging or goods for resale


% Capital expenditure eligible for grants

These grants refer to capital provided by State and aid bodies. They are usually contributions towards capital expenditure programmes and are usually only repayable in very exceptional circumstances. Indicate the percentages of projected Capital expenditure and New finance leases (if any) likely to be eligible for grants.


Accumulated depreciation

Enter Accumulated depreciation as at the planned opening balance sheet date. The opening Cost or valuation less opening Accumulated depreciation will be automatically entered into the opening balance sheet.


Accumulated grant amortisation

Indicate Accumulated grant amortisation up to the planned opening balance sheet date. The projected grant amortisation rate will be identical to that used for depreciation.

The opening Total capital grants less the opening Accumulated grant amortisation will be automatically entered into the opening balance sheet.


Allocation of other direct costs on the basis of direct payroll costs

These costs are allocated between the main sales groups on the basis of their respective Direct payroll costs. If no Direct payroll costs were specified, costs are allocated between the groups on the basis of their respective sales.


Approximate percent of total payroll costs relating to income taxes & social insurances for all staff

Enter estimates which include income taxes and employees' social taxes (e.g. NIC, PRSI etc.). Typically, the percentages could be in the range of 30% to 40%. These percentages will be automatically deducted from total monthly payroll costs and will be paid to the State one month in arrears.


Average payroll cost

Include gross wages and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Average VAT rate for non-pay expenses

Exclude all payroll expenses.


Average VAT rates on sales

Refers to Value Added Tax (VAT) and may not be relevant to all jurisdictions. Estimate weighted average rates to take account of mixed rates, exports etc. These rates need not correspond to official rates.


Calculate VAT on an invoice or cash-received basis

In most tax regimes, businesses below a specified size can opt to calculate their VAT liabilities on a cash-received basis rather than the normal invoice-basis.


Capex

Capex = Capital Expenditure


Capital expenditure

Indicate planned Capital expenditure (Capex) commitments and the number of month's credit to be secured (enter 0,1,2,3 or 4 below the "X").


Capital grant rate as % eligible fixed assets

Indicate the grant rate likely to apply to Capital expenditure eligible for grants and to New finance leases (if any). Use the cell below the "X" to enter the number of months (0 to 4) by which the payment of grants will be delayed. Only enter integers - 0,1,2,3 or 4.


Changes (+/-) in accruals

Use + for an increase and - for decrease.  The projected cashflows and balance sheets are automatically adjusted to take account of these changes.


Changes (+/-) in prepayments

Use + for an increase and - for decrease. The projected cashflows and balance sheets are automatically adjusted to take account of these changes.


Clerical payroll

Include gross salaries and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Cost or valuation

Enter the total original cost (not the written-down value) as at the planned opening balance sheet date.


Depreciation & grant amortisation rate

Enter the average annual depreciation rate that will apply throughout the year. The same rate will be used to amortise the value of any non-repayable capital grants/funding received from the State or other aid bodies.


Desired finished stocks (000 units or equivalent sales value in £000)

These stock levels were derived from the sales forecasts and Desired finished stock percentages. Enter 'best' estimates for the opening stock levels to help Exl-Plan determine the total Finished goods required (Report No. 2) to meet sales and any stock-building planned for the first month. Set all values to zero if business is distribution or labour-only services.


Desired finished stocks as % sales for X months ahead

Set target stock levels for each main sales group. Express targets as percentages of projected sales for between 0 and 4 months ahead. Enter the month numbers (0, 1, 2, 3 or 4) in the cells immediately below the X. Set all values to zero if business is in distribution or labour-only services.


Desired stocks of materials/packaging or goods for resale

Enter opening values and make estimates for subsequent months. The total opening amount for the main sales groups is used in the opening balance sheet.


Direct cost per unit

Indicates unit costs based on Total direct costs and on projected Finished stocks required (as calculated on Report No. 2).

If sales volumes were NOT used in Report No. 1, then the unit costs correspond to decimal percentages for selling prices (previously set at 1.00). For example, a cost of 0.77 would signify costs equivalent to 77% of selling prices.


Direct manpower

Assign numbers to main sales groups as far as practicable. This will help improve the accuracy of calculations of the relative profitability of the groups. If all direct costs should be excluded from Cost of sales, set these values to zero.


Dividends for the current year

Use the cell below the "X" to defer payment of dividends by up to 4 months by entering 0, 1,2,3, or 4. This item does not apply when Exl-Plan has been set up for businesses operating as sole traders or partnerships.


Effective corporation tax rate for year

Enter estimated rate after taking account of any liabilities etc. This item does not apply when Exl-Plan has been set up for businesses operating as sole traders or partnerships.


Finance leases from prior years

Charges refer to the 'interest' component of projected payments to leasors in respect of finance leases taken out in previous years. Charges for projected new leases should be entered in Report No. 5.

Payments refer to the 'capital' component of projected payments to leasors in respect of finance leases taken out in previous years. Capital payments for projected new leases should be entered in Report No. 5.


Finished goods required

These values have been expressed as units or valued on the basis of estimated selling prices and have been calculated from Sales + closing Finished stocks - opening Finished stocks.

If there is an unexpected dramatic change in projected requirements for the first month, review the estimated values of opening Finished stocks in Report No. 1.


Finished stocks

Enter the opening stock values for each main sales group at the opening balance sheet date. The total value is incorporated in Exl-Plan's opening balance sheet. These values must be consistent with any estimates for opening Desired finished stocks, based on selling prices, previously entered in Report No. 1.

For subsequent months, the values for Finished stocks are derived from the Direct costs per unit and the Desired finished stocks (if any) contained in Report No. 1.


Indirect payroll

Include gross salaries and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Interest payable on 'other loans'

Enter estimated values. These payments may need to be adjusted to take account of any increases or decreases in outstanding balances for Other loans.

The difference between Other loans and Longterm debt is that the actual amount of interest payable is specified for the former and for the latter it is calculated automatically based on closing monthly balances and the specified interest rates.


Interest rates

Enter annual, not monthly, rates.


Longterm debt

Enter annual rate of interest. The interest amount for Longterm debt is derived from the interest rate indicated above. It is calculated automatically based on closing monthly balances and the specified interest rates.

The difference between Other loans and Longterm debt is that the actual amount of interest payable is specified for the former and for the latter it is calculated automatically based on closing monthly balances and the specified interest rates.


Management payroll

Include gross salaries, bonuses etc. and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Mark months when VAT is paid to State

Enter zeros (0s) for all months that are not VAT payment months.

In the UK, VAT is usually paid every three months and in Ireland it is normally paid every two months. Very large businesses pay VAT every month.


Net selling prices

Enter prices net of sales taxes, VAT and discounts. Use weighted average prices to take account of mix within each main sales group. Enter £1.00 if NOT using sales volumes.


New finance lease capital payments

These refer to the 'capital' component of payments to leasors.


New finance lease charges

These refer to the 'interest' component of payments to leasors.


New finance leases

Assets acquired through finance (or capital) leasing are treated as fixed asset purchases and corresponding liabilities are automatically created to reflect these purchases. New finance leases are treated as capital expenditure when calculating Capital grants receivable.


Operational overheads

Use the cells below the "X" to enter the number of months (0 to 4) of credit being obtained on the corresponding cost items. Only enter integers - 0, 1, 2, 3 or 4. The unlocked descriptions can be changed or ignored.


Other direct costs

As far as practicable, enter only variable or semi-variable costs that can be attributed to production and operations here. Fixed costs are treated as overheads and entered into Report No. 4. Use the cells below the "X" to enter the number of months (0 to 4) of credit being obtained on these cost items. Enter only integers (0, 1, 2, 3 or 4).

If all direct costs (aside from the cost of materials or goods for resale) are conventionally excluded from Cost of sales, set all values for Other direct costs to zero and enter the overhead costs in Report No. 4.


Percent of all non-payroll expenses (overheads, HP etc.) subject to VAT

Enter estimates (HP = Hire purchase payments). These are likely to exceed 90% as almost all categories of inputs are covered to VAT. The main exceptions are insurance payments and, possibly, rent.


Phasing out of opening balance sheet items

If ERROR appears in column B for a specific row, the total amount being phased out during the year for the row differs from the total shown in column F (entered via the opening balance sheet).


Proportions payable within one year

Use this table to apportion Longterm debt, Other loans and Finance leases between Current and Longterm Liabilities within the projected monthly balance sheets. Enter approximate percentages that take account of repayments or increases expected during the initial two years. If this feature is not required set all the percentages to zero.


Revenue grants receivable

These refer to non-repayable grants or subsidies for training, employment, consultancy etc. provided by the State or aid agencies. These grants impact on the projected P&Ls. They should not be confused with Capital grants entered in Report No. 5 and which are processed via the projected balance sheets.


Sale of fixed assets

Give details of any planned fixed asset disposals.

The Proceeds refer to the consideration to be received. The Cost refers to the original cost (or latest valuation) of the fixed assets being disposed of. The Accumulated depreciation covers the total depreciation of the assets being disposed of right up to the disposal date.


Sales

Enter sales volumes as 000s or, if using £000, set Net selling prices in table lower down to £1.00. You can swap from 000s and £/unit to units and £000/unit. The unlocked descriptions for the sales sub-groups can be changed.

To change descriptions for the main groups, use *Setup | Enter Basic Model Info.


Sales payroll

Include gross salaries and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Seasonal Index

Values in this row can be used to allocate a total over rows assigned to assumption values within this report. Enter appropriate values (e.g. work days per month) within the seasonal index and then use *Assumptions | Seasonal Assumptions to pro-rata a total value across the assumption rows to the right of the cursor. Note: Cursor must be located within a boxed in and unlocked block.


Selling & distribution costs

Use the cells below the "X" to enter the number of months (0 to 4) of credit being obtained on the corresponding cost items. Only enter integers - 0, 1, 2, 3 or 4. The unlocked descriptions can be changed or ignored.


Staff payroll

Include gross salaries and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Supervision payroll

Include gross salaries and all payments made by employer to cover an employee's social taxes (e.g. NIC, PRSI etc.) and related add-on payroll costs.


Total capital grants

Indicate the Total capital grants received up to the planned opening balance sheet date.

The opening Total capital grants less the opening Accumulated grant amortisation will be automatically entered into the opening balance sheet.


Treatment of trade creditors & debtors for current year

Indicate the percentage distribution of credit for each item. ERROR in column L for a specific row indicates that the percentage distribution of credit for that row does not equal 100% exactly.


Unit cost of materials/packaging or goods for resale

If sales volumes are not being used, enter unit costs as £0.xx to correspond to xx%. For example, 0.55 would indicate that costs are equivalent to 55% of selling prices (provided Net selling prices have already been set at £1.00 in the price table in Report No. 1).


Even a new user of Exl-Plan should be able to produce first-cut projections in less than 15 minutes.


Fourth and fifth year projections are  available with the Pro & Super versions of Exl-Plan.


Free, Micro, Lite, Pro & Super  are suitable for businesses ranging in size from very small to major.


Excel for Windows (versions 5, 7, 8, 95, 97, 2000 or higher) running with Windows 3. or Windows 95/98/NT on a PC with a 486/50Mhz or higher processor, 16+ MB of memory, VGA or better display and 10 MB disk space.


This site also contains details of all new versions of Exl-Plan as soon as they become available.


Typically, it makes monthly projections for the first year and less detailed projections for the following years.


A financial model and its forecasts should contribute to, but never dictate, the contents of a business plan.


An impressive set of financial projections is of little benefit if unsupported by research or simply based on speculation or wishful thinking.


Note that assumption entry facilities only operate within the boxed-in areas assigned to assumption values.


Unlocked values are shown in blue and located in rows opposite ->> or <<->> arrows.


A warning is issued if all assumption values have not been removed when clearing all assumption reports.


A warning message will be issued if it finds that any assumption values have not been removed.


Note that formulae for calculating PAYE in the Monthly Cashflow Projections extend from column G to hidden column S.


If there is an imbalance, CHECK balance sheet balances at the very bottom will indicate the magnitude and direction of the imbalance.